Member Avatar for themaj

A question for VB.NET - Access DB folks here, although some might consider a strictly DB question.

Is there an order of operation when a SQL query 'does its' thing' relating to the 'WHERE" clause with multiple parameters? In other words, could you rearrange the elements [filters] of the WHERE clause to enhance the speed.

I have a query that just seems slow; it responds with the answer in 1.3 seconds...but that's not really important here.

I'm not being sexiest here; just thought this illustration was easy to imagine.
With all else being equal, and in a normal coed college environment, which query would be faster?

SELECT All_Students FROM Enrollment

WHERE [gender=female], [eye color = green], [hair color = brown], [height >= 170cm]

..... OR....

WHERE [height >= 170cm], [hair color = brown], [hair color = green], [gender=female]

Considering that [gender] would be the most successful filter, would it's placement as the first parameter enhance the speed or does the query just return records that fit the whole list of parms?

I'm self-taught in VB.NET & Access so please don't hesitate to explain you answer/opinion, or point me to more info.

Regards, appreciate your input

  • 2 Contributors
  • 1 Reply
  • 237 Views
  • 28 Minutes Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

Considering that [gender] would be the most successful filter, would it's placement as the first parameter enhance the speed or does the query just return records that fit the whole list of parms?

This used to be true (even for database servers), but I am not sure how Access has evolved it's querying. I suggest you analyze performance and find out.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.