CODEWITHSUNDEEP
t-sql
gmail user
gmail user

Reputation: 2783

query large table

Is it better to query large table (> 1.5 million rows) 3 times or to put 3 condition in the same query? The sample where clause is below. There are similar or more complicated where clauses. My hunch is to put all of them in same where. But need some input from community. Thanks in advance.

where (a =1 or b=1) and (a=2 or b=3) and (c=1 or d=3)

Upvotes: 2

Views: 185

Answers (4)

JNK
JNK

Reputation: 65147

Put it all in one query.

In addition to reduced overhead, you will eliminate duplicates this way. If you have a row that matches more than one of your criteria, it will appear twice in your final result set if you query multiple times.

Upvotes: 1

The Ed R
The Ed R

Reputation: 326

It all depends on your schema (datatypes, keys, indicies etc), generally speaking I'd say you would be better off adding all the conditions to the same where clause so the query optimizer has as much information as possible to work with. The best way to find out is to run some tests to see which approach yields the best performance on your data/schema.

Upvotes: 1

Eric
Eric

Reputation: 95103

It's quicker to put them all in the same where clause, generally. However, each query is different, and make sure you check the query plans for each query to ensure you're getting the best performance out of them you can be.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

A single trip to the database is definitely preferable. Make sure you index the a, b, c and d columns and you should be fine.

Upvotes: 5

Related Questions