performance problem 2

[fikir]

this query is taking time

select * from tbl1
where id in (select id from splitFunction(@listOfIds, ',')
or not exists(select id from splitFunction(@listOfIds, ',')

@listOfIds ia a variable with list of id's and what the query does is select ids if they exist in the list, but if the @listOfIds is null It will select every Id in the tbl1 table

split function splits the comma separated list into separate values

is there a better way to write the statement

Thanks,

 

[SQLSister]

check your execution plan, but I'd guess this query isn't efficiently using your indexes. Wouldn't be surprised to see a table scan instead.

First I would encapsulate this in a stored proc. It would put the list of ids from the split function into a table variable or temp table (I believe you can use a table variable for this in 2005, but may need to use a temp table in 2000). This way the expensive split operation is only done once.

Then I would if logic of some type. If count(*) >0 then exceute the query by joining to the temp table not using in (which is less efficient than a join usually). Else select all the records. Also I would not use Select * as it is usually a bad practice to select all the fields when you relaly only want some of them and never use uit when you join as you will by definintion be selecting some information twice (the join field(s) is (are) in both tables). The less you send over the network, the faster the query.

I'm sure there are other ways to approach this as well.

"NOTHING is more important in a database than integrity." ESquared

 

[fikir]

Thank you sqlsister

the table variable cuts the time by half

 

[gmmastros]

Wow!

SQLSister was kind enough to answer your question and the result was a dramatic performance improvement. I think this deserves a special kind of thank you. I urge you to scroll up a bit. Notice the link that says, "[!]Thank SQLSister for this valuable post![/!]"? Please click that link, and then confirm it on the next screen.

I bet SQLSister will get a warm fuzzy about it.

-George

"the screen with the little boxes in the window." - Moron

 

[fikir]

you are right George, I should have done that,

Thank you all agian

 

[SQLSister]

SQl Sister is still hurting from injury at Dragon Con. I need all the warm fuzzies I can get. Thanks for the star.

"NOTHING is more important in a database than integrity." ESquared