SQL search or query 1

[ledzepe]

hello everybody,

i would just like to solicit your opinion on wheather or not our consultant is telling us the truth or just taking us for a ride. i am not well verse on sql.

we have a win2003 server running sql2000 and they made a search program so that we can find stuff on the database. our database has 60 or so fields, like acct name, number, ship to ship from, date, etc. on the web interface, we about 16 fields that we can use to do a search on but we only use 3 fields. our consultant told us that that was the original design.

we ask if they can change it so that we can use all 16 field but he said that the search will take longer if there's more than 4 fields being used to filter the result. is this true or somewhat true? where can i find some good reference material so that i could make even a rudimentary query or search?

thanks for all the answers/opinions.

 

[bborissov]

If the Database has not optimized to use that additional 12 fields (with proper indexes for the tables), that search could be longer than searching only by these 4 fields already optimized.
BTW Table can have 60 fields not database. Database contains tables, relations between them , Rules, Triggers, SPs etc. So MAYBE your consultant is right.
I can't tell you for sure w/o knowing you Database.
That's my opinion only, no need to be true.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

Without knowing the data structure, indexes or how the search currently work or how he anticipates implementing the change it is hard to say if there will be a negligible performance impact or a large one.

In general I would say it is possible that it might make the search take longer but that may be so small an increase that your users won't notice or it could be very bad.

Depending on the currrent indexes it could also be possible the search would be faster.

Additionally, if you have to add indexes so that 16 fields have indexes (or the search could potentially be very slow when the non-indexed fields are the search criteria), then inserts might be slowed somewhat. (I do find that normally users will prefer a slightly slower insert over a slower search, but if it causes timeouts then there would be a real issue with doing this.)

Only you can know your real business needs though. If you must be able to search by 16 fields at the same time, then it is up to the consultant to work to find the most efficient solution. You may also have to get your users to realize that if they need this functionality, the search may take longer. Often times this will make the users decide they don't need that additional functionality after all.

I did a search one time that had 30 or 40 possible fields to search on. To speed it up, we picked out the two or three most common searches (that accounted for 90+% of the seraches) and wrote code just for them and then wrote the big one (which had to access joins to more tables because of the search fields) and used if statements to decide which to run. That ran extremly fast for the most common searches and only took a lot more time for the less common.


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

 

[ledzepe]

thank you SQLSister, i work for a freight company and do to an effective search we always need a date range, origin, destination and shipper so that's 5 fields already. we need more fields to really narrow down the search. i will bring up the small search and big search option that you mentioned on our next meeting.