Question about SELECT stm performance 3

[EwS]

Let's say I have 10 customers that I need to select from my SQL database. I'm using a join in my select statement and the tables are pretty big. I have some processing to be done for each customer after it's been selected. I'd like to know what would be better in terms of performance (speed):

a) have one select statement listing all the customers in the where clause
OR
b) do a select statement for each customer.

I'm trying to do b) so that I don't have to use looping through an array list too many times (I would have to maintain three array lists because of the processing that I have to do for each customer), which could potentially slow down the program if I had much more than 10 customers, but wouldn't it still be faster than executing a select stm for each customer?

Thank you for your opinion.

 

[chrissie1]

I would go for a. But that is based on your explination not actual code.

Christiaan Baes
Belgium

"My old site" - Me

 

[ThatRickGuy]

The guys in the SQLServer forum could probably tell you more aswell, but I would agree with Chrissie and go for option A.

With option B you'll add the network traffic overhead and request/response time to each of the 10 queries as opposed to just once for option A. Also, depending on how your table/indexes/query is set up, it could mean the difference between 1 table scan and 10 table scans. And even a non-DBA guy like me knows that table scans are the mark of death when database performance is important.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[AlexCuse]

Use a stored procedure and give it ten optional parameters.

Or if you feel adventurous, what I would do is pass a variable length comma separated list containing all your customer ID's, and split it into a table on the SQL side so you only need one join rather than a bunch of OR nonsense (which can also slow down your query)

If you decide to go this route, I believe this is the definitive tek-tips thread discussing split functions:

http://www.tek-tips.com/viewthread.cfm?qid=1389789&page=1

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom