September 9, 2003 at 10:10 am
Hi there,
I am having trouble for running dynamic SQL with performance issue. I have a loop which will capture a value that using in a dynamic sql. In which, if the record exist, then loop for the next value. Otherwise, do something using this captured value... I am thinking, is this possible for the dynamic sql stop searching as soon as it found one record; for performance issue. I tried with IF EXISTS (SELECT ...) but this does not support dynamic sql. I also tried with sp_executesql but it does not support IF EXISTS keyword. Is there anyone could give me an idea what should I do?
Thanks,
AC
September 9, 2003 at 7:30 pm
Hi
The sp_executesql should be fine, use this though to say, write to a variable and check the contents of the variable instead of attempting an IF ..., say, do a count(*) and if zero, thats TRUE and any other value FALSE (for arguement sake).
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 10, 2003 at 6:49 am
Can you post your query
Far away is close at hand in the images of elsewhere.
Anon.
September 10, 2003 at 7:29 am
The following code will run untill condition is met
WHILE EXISTS(SELECT {field} FROM MyTable WHERE {<condition>}) BEGIN
.
.
. code in here
.
.
END
Will
September 10, 2003 at 8:01 am
I did it this way but not really satisfied because it seems to me that it is not the most efficient way. I still like my initial idea for a sql statement which search for any record which matched the criteria, then stop db engine search and go for the next loop. Anyway here is my code:
WHILE 1 = 2
BEGIN
SET @mvchSQL = 'SELECT @mintRecordCount = COUNT(T1.PROJECT_ID) FROM ' + @vchTableName
SET @mvchSQL = @mvchSQL + ' T1,PSSL_RESERVE_ENTITY T2 WHERE T1.PROJECT_ID = T2.PROJECT_ID AND T1.ENTITY_ID = T2.ENTITY_ID'
SET @mvchSQL = @mvchSQL + ' AND T1.PROJECT_ID = ' + @C_QUOTE + @PROJECT_ID_IN + @C_QUOTE + ' AND T2.PROPERTY_ID = ' + @C_QUOTE + @PROPERTY_ID_IN + @C_QUOTE
-- excecute to get record count
EXEC sp_executesql @mvchSQL, N'@mintRecordCount INT OUT', @mintRecordCount OUT
-- assign flag with true if found records
IF @mintRecordCount >= 1
SET @ENTITY_EXIST_FLAG = 'Y'
END
Anyone has a better solution?
Thanks,
AC
September 10, 2003 at 9:04 am
Still not clear
Why 'WHILE 1 = 2' this means the code is never actioned
quote:
I have a loop which will capture a value that using in a dynamic sql. In which, if the record exist, then loop for the next value. Otherwise, do something using this captured value...
if the record does not exists what is captured?
what are you using @ENTITY_EXIST_FLAG for?
Am I missing somrthing here!
This will use dynamic sql to execute IF EXISTS
SET @mvchSQL = 'IF EXISTS (SELECT T1.PROJECT_ID FROM ' + @vchTableName
SET @mvchSQL = @mvchSQL + ' T1,PSSL_RESERVE_ENTITY T2'
SET @mvchSQL = @mvchSQL + ' WHERE T1.PROJECT_ID = T2.PROJECT_ID'
SET @mvchSQL = @mvchSQL + ' AND T1.ENTITY_ID = T2.ENTITY_ID'
SET @mvchSQL = @mvchSQL + ' AND T1.PROJECT_ID = ' + @C_QUOTE + @PROJECT_ID_IN + @C_QUOTE
SET @mvchSQL = @mvchSQL + ' AND T2.PROPERTY_ID = ' + @C_QUOTE + @PROPERTY_ID_IN + @C_QUOTE
SET @mvchSQL = @mvchSQL + ') SET @ENTITY_EXIST_FLAG = ''Y'''
SET @ENTITY_EXIST_FLAG = 'N'
exec sp_executesql @mvchSQL, N'@ENTITY_EXIST_FLAG char(1) OUT', @ENTITY_EXIST_FLAG OUT
IF @ENTITY_EXIST_FLAG = 'Y'
do something here....
Edited by - davidburrows on 09/10/2003 09:05:00 AM
Far away is close at hand in the images of elsewhere.
Anon.
September 10, 2003 at 9:27 am
Oh, I am so sorry for the confusion of some missing codes and logic. Since my focus was in the dynamic sql string and the performance for the use of the sql statement. Of course, I will not construct a dead loop logic block. Can you explain to me what is the behaviour of IF EXISTS? Will it searches until the last record even though there is a matched record found prior? My idea is once it found a matched record the search should stop and loop for the next value, else do something and then loop for the next value. Does this make sense now? Again it is just a general idea for the logic that I have to implement.
Thanks,
AC
September 11, 2003 at 3:26 am
From my understanding and what I remember from other threads on this site, you are correct. IF EXISTS will stop at the first successful match. How long it takes will depend on the efficiency of the query.
Far away is close at hand in the images of elsewhere.
Anon.
September 11, 2003 at 3:38 am
quote:
From my understanding and what I remember from other threads on this site, you are correct. IF EXISTS will stop at the first successful match. How long it takes will depend on the efficiency of the query.
again applying knowledge derived from this site.
To piggy-back (hey, I really like this phrase, Brian!) on David.
IF EXISTS is almost always faster than say a SELECT COUNT(), because it has a boolean condition and stops as soon as it is true, eg. first match while SELECT COUNT runs through the set even it is has already found a match
Frank
Wenn Englisch zu schwierig ist?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 11, 2003 at 8:26 am
Thank you guys, that is all I needed to know. Many thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply