Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
netcop
Starting Member
12 Posts |
 Posted - 2002-07-25 : 14:33:00
|
Hi there!I want to optimize the query below. I want to check if there are entries in the table. Are there better solutions? This one consumes much time.The variable is used, because it's checked later in sp again.select @counter = (select count(1) from #mytable) if @counter= 0 begin ... end ThxEdited by - netcop on 07/25/2002 14:36:14 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-25 : 14:49:23
|
You can do this:IF EXISTS (SELECT * FROM #mytable)BEGIN...ENDIf there is at least one row in the table, the EXISTS clause will return immediately and run the code. And if it IS empty, then it will also return immediately and execute the code.OK, I COMPLETELY screwed up my explanation, so let's try this again:  IF NOT EXISTS (SELECT * FROM #mytable)BEGIN...ENDThis is equivalent to your original. If there are NO rows in the table, then EXISTS will return immediately and execute the code in the BEGIN...END block. If there are ANY rows in the table, it will also return immediately but will SKIP the code in the BEGIN...END block.Don't use Count() unless you truly need to count something, because it will actually process the query (and without a WHERE clause it will access the entire table)Edited by - robvolk on 07/25/2002 14:57:05 |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-25 : 14:50:27
|
Well, it doesn't get much more optimized than that....see above..Depending on the ddl in your block, you could use a "where not exists (select 1 from #mytable)" clause and bag the variable.If this is the slowest piece of code you have, you are pretty well off ...<O>Edited by - Page47 on 07/25/2002 15:02:08 |
|
|
|
|
|
|
Check for entries in table - how to optimize query