Re: Use Estimated execution plan - Database Discussion Boards

David Mujica1-May-09 9:45

1-May-09 9:45

If your start_date and end_date are defined as type "date", I believe you need to do a TRUNC(start_date) to compare the date. The datatype may actually contain both date and time.

Also, my preference is to use parenthesis around my logical conditions so that I know that they will be evaluated properly.

For example:
WHERE (Start_date <= '01-APR-2008' AND END_DATE >= '01-MAY-2009') OR END_DATE IS NULL

Re: SQL

newbie123456789101-May-09 9:59

newbie12345678910

1-May-09 9:59

THANKS!!!!

generate & compare id from table in sql storeprocedure

premprakashbhati30-Apr-09 22:26

premprakashbhati

30-Apr-09 22:26

"select max(centreid) from regionmaster where centreid like '" & intBranchPrefix & "%'", DBConnection, adOpenKeyset, adLockOptimistic 
         If rsMain.RecordCount > 0 Then 
            intID = Mid(rsMain(0), 3, 13) 
        End If 
         intCentreID = intBranchPrefix & (intID + 1)

this is my sql command in vb6 aplication.here i have to create a storeprocedure of this command which create centreid by taking BranchPrefix from application.i have created a storeprocedure but iam getting an error sayingArgument data type decimal is invalid for argument 1 of substring function.in below storeprocedure Note:i have centreid in table in binary(13,2)

create proc GenerateCentreID @intBranchPrefix varchar(20) 
as  
declare @MaxCentreID bigint,@intID varchar(20)  
set @MaxCentreID=(select convert(bigint,max(substring(centreid,3,13)))  
                  from regionmaster  
                  where centreid like @intBranchPrefix+'%' 
                  )  
  
set @intID=@intBranchPrefix + convert(varchar(13),@MaxCentreID+1) 
select @intID 
  
go

stored procedure is fast, but slow from code

Meysam Mahfouzi30-Apr-09 20:47

Meysam Mahfouzi

30-Apr-09 20:47

I have got a stored procedure which executes very fast in Sql Server Management Studio, but when I call it with the same parameters from C#, it executes slower and sometimes even throws timeout exception.

Does anybody know the reason?

hint: I have noticed that when I comment out one of the conditions in where clause, the query executes very fast both on SSMS and C#.
Another point is that when I remove the comment and put the condition back in the query (which causes the stored procedure to compile again) the query execution becomes a little faster in C#, but after a while it becomes slow again (all with the same parameters)

Re: stored procedure is fast, but slow from code

Henry Minute1-May-09 0:04

Henry Minute

1-May-09 0:04

I think that for anybody to stand a chance of answering your question, you should edit your post and include the stored procedure and the c# code that calls it.

Please do NOT reply to me, this is just some advice to help you get an answer. Smile | :)

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Use Estimated execution plan

David Mujica1-May-09 3:24

David Mujica

1-May-09 3:24

May I suggest ...

In SQL Server Studio Manager, execute your stored procedure, but under the menu "Query" choose the option to "Display Esitmated Execution Plan". This will give you great insight into how the SQL server is going to retrieve your data. You want to avoid large "scans" of tables, try to take advantage of indexes.

Also, you may want to look into SQL Profiler and watch what is going on while your code executes.

Re: Use Estimated execution plan

Meysam Mahfouzi1-May-09 5:53

Meysam Mahfouzi

1-May-09 5:53

Thanks for your advice,

All the indexes are being used (index seek) and no table scan is happening. That's why the query gets executed very fast in Sql Server. That's why I wonder what the difference is when I execute it from code with the same parameters.

Re: Use Estimated execution plan

David Mujica1-May-09 7:55

David Mujica

1-May-09 7:55

I would now look into the SQL Profiler, because you can now monitor how your application is interacting with the database. Things to check on the client side would be network traffic, memory usage and CPU usage while this is executing.

Sounds like a fun challenge. Cool | :cool:

Re: stored procedure is fast, but slow from code

Mycroft Holmes1-May-09 14:52

Mycroft Holmes

1-May-09 14:52

I found something recently that discussed arith_abort (I think, the link is at work) which is set on by default, setting it to off apparently made a difference to some procs that showed this behavior.

I have seen this behavior and after doing all the optimising possible the proc was still slow, that's when I started chasing down the more esoteric suggestions.

If I find the link I will post it Monday....

Never underestimate the power of human stupidity
RAH

MySQL: how to call a stored proc with OUT param

devvvy30-Apr-09 20:15

devvvy

30-Apr-09 20:15

hi there,

how can you call a stored proc with OUT param in MySQL?

CREATE PROCEDURE spListUser (
...
PrimaryEmailFilter nvarchar(255),
..
OUT NumItemsSelected int
)
BEGIN
...
END;

I tried different things none work.

<br />
CALL spListUser (<br />
...<br />
	@NumItemsSelected<br />
);<br />
<br />
CALL spListUser (<br />
...<br />
	OUT @NumItemsSelected<br />
);<br />
<br />
CALL spListUser (<br />
...<br />
	@NumItemsSelected OUT<br />
);<br />

In the end, I figured that it worked with Command line client but not with TOAD. What the hell is this? Anybody come across this before?

Thanks

dev

Microsoft SQL 2008

Ivan200930-Apr-09 10:20

Ivan2009

30-Apr-09 10:20

How can I go about building a database for say, a toy company? Using the microsoft SQL 2008? I know SQL commands whcih Ihave used to retrieve data using VB script from an already built website.
But I want to build my own DB and then connect it to my .NET framework.

Re: Microsoft SQL 2008

Henry Minute30-Apr-09 11:43

Henry Minute

30-Apr-09 11:43

Well one way to start would be by building ER diagrams.

Re: Microsoft SQL 2008

Ivan200930-Apr-09 14:12

Ivan2009

30-Apr-09 14:12

Yeah Henry, that would be a good start. But then, how do I start up SQL 2008 and how do I get the Microsoft SQL editor? I mean, i see a downloaded link that says SQL 2008 on my desktop after I installed Visual Developer. But, I am unanle to figure out where do i go from there to build my DB??

Re: Microsoft SQL 2008

Mycroft Holmes30-Apr-09 17:50

Mycroft Holmes

30-Apr-09 17:50

Buy a book on SQL, your question is unanswerable in a forum, you are asking someone to teach you how to build a database, this can take years to perfect and certainly weeks/months to get the basics.

Never underestimate the power of human stupidity
RAH

Re: Microsoft SQL 2008

Ivan20091-May-09 6:28

Ivan2009

1-May-09 6:28

Thanks for the Curt reply Mycroft.

system table sysmergesubscriptions: replacements for distributor and srvid

Roland Bär30-Apr-09 6:03

Roland Bär

30-Apr-09 6:03

Hi
The system table sysmergesubscriptions has changed from SQL Server 2000 to SQL Server 2005. The columns distributor and srvid are no more available. I have a stored procedure that used this columns that is not working when it should be used on an SQL Server 2005 or 2008.

Are there any replacements for this columns and is it possible to use them also on SQL Server 2000?

Here is the query that uses this columns:

DECLARE repl_cur CURSOR FOR
		SELECT
			mp.name,
			me.srvname,
			mp.publisher, 
			mp.publisher_db,
			ms.db_name,
			ms.subscription_type,
			ms.distributor
		FROM
			sysmergepublications mp 
		INNER JOIN
			sysmergesubscriptions ms 
			ON ms.pubid = mp.pubid 
		INNER JOIN 
			master.dbo.sysservers me
			ON ms.srvid = me.srvid
		INNER JOIN 
			sysmergearticles ma 
			ON ma.pubid = ms.pubid 
		WHERE 
			ma.name = 'PasswordCounter' 
			AND ms.subscription_type = 1

Thanks for your help

Greets
Roland

Hi! I'm a signature virus. Copy me into your sig file and help me spread!

Follow your Euro notes in their tracks

linked server error in sql server

Maddie from Dartford30-Apr-09 3:32

Maddie from Dartford

30-Apr-09 3:32

Hello,
I got a SQL 2005 database back up file which i restored in my VS2005 SQL studio express to use in my code. I found that some of the stored procedure are using linked server. I created all required linked server object in studio express but still when i try to execute the stored procedure, i get this error:

"The OLE DB provider "MSDASQL" for linked server "LinkedServerName" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Procedure rpStoredProcedureName, Line 18
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName"."

Any idea to resolve this problem? what else i need to do?

Thanks in Advance.

Re: linked server error in sql server

Mycroft Holmes30-Apr-09 17:54

Mycroft Holmes

30-Apr-09 17:54

gajesh wrote:
I created all required linked server object in studio express

Does Express support linked servers - I would be surprised if it does.
Are all the linked servers accessible to your system?

Never underestimate the power of human stupidity
RAH

select where only

foryou30-Apr-09 0:58

foryou

30-Apr-09 0:58

Hi!

I have a table Validation:
Id_student --- - id_semestre ----- id_validation
102----------- - 1 ---------------- 4
102----------- - 1 ---------------- 3 --
102----------- - 2 ---------------- 4
103----------- - 1 ---------------- 4
104----------- - 2 ---------------- 3
104----------- - 2 ---------------- 4

I want to select students who have in semestre 2 id_validation = 4 only .
in the example i want have :
102 students, not 102 and 104
thank you.

Re: select where only

scottgp30-Apr-09 1:46

scottgp

30-Apr-09 1:46

This works (using a correlated subquery), but I'm sure it's not the only way to get the correct results:

select v1.id_student from validation v1
where v1.id_semestre = 2 and v1.id_validation=4
and not exists (select id_student from validation v2
where id_semestre = 2 and id_validation<>4
and V1.ID_STUDENT = V2.ID_STUDENT )

Scott

Re: select where only

foryou30-Apr-09 2:56

foryou

30-Apr-09 2:56

Thanks scottgp it works. Smile | :)

Update datagridview

hitesh.kalra29-Apr-09 21:41

hitesh.kalra

29-Apr-09 21:41

If I am working manually to create columns and add it on datagrid view at run time it is not updating through olddbcommandbulder command. But when I am using this code

"datagridview.datasource= ds.tables[0].defaultview;"

then it is updating. But how will I updating when I work manually. Plz give a code in c# .net.

thanx in advance.

Re: Update datagridview

Mycroft Holmes29-Apr-09 22:03

Mycroft Holmes

29-Apr-09 22:03

i give up!

Never underestimate the power of human stupidity
RAH

Re: Update datagridview

Spunky Coder29-Apr-09 22:13

Spunky Coder

29-Apr-09 22:13

Post all your GridView and the related troubles in C# forum.

"Don't worry if it doesn't work right. If everything did, you'd be out of a job." (Mosher's Law of Software Engineering)