Thank you Marc for the amazing link and Terry for the additional insight. Truly appreciated. CFUG Toronto has got to be, by far, the best UG I'm part of!
> From: [EMAIL PROTECTED] > Reply-To: [EMAIL PROTECTED] > Date: Wed, 12 Jun 2002 10:37:06 -0400 > To: <[EMAIL PROTECTED]> > Subject: RE: [CFTALKTor] searching 65 million records with MSSQL > > The issue that it got slow after the OR's were added suggests to me that the > OR's are preventing the usage of indexes. > > SELECT acolumn, bcolumn, ccolumn > FROM tablename > WHERE > (acolumn = 'boo' AND bcolumn = 'yikes') OR > (bcolumn = 'haha' AND ccolumn = 'clown') > > can run a lot slower then: > SELECT acolumn, bcolumn, ccolumn > FROM tablename > WHERE (acolumn = 'boo' AND bcolumn = 'yikes') > UNION > SELECT acolumn, bcolumn, ccolumn > FROM tablename > WHERE (bcolumn = 'haha' AND ccolumn = 'clown') > > Despite the fact the union in theory scans the table twice, if the scan can > use an index 2 index scans is still MUCH faster then one table traverse. > > NOTE: You may need a DISTINCT clause to purge duplicates for records that > satisfy BOTH of the UNION'd queries, some engines (correctly) imply the > DISTINCT, some do not. I wouldn't trust MSSQL to be smart enough to > eliminate the duplicates rows, which leaves you in a bad place if you want > to allow duplicate rows of VALUES but not duplicate TUPLES. > > Terry Fielder > Network Engineer > Great Gulf Homes / Ashton Woods Homes > [EMAIL PROTECTED] > >> -----Original Message----- >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On >> Behalf Of A. Karl Zarudny >> Sent: Wednesday, June 12, 2002 10:02 AM >> To: [EMAIL PROTECTED] >> Subject: Re: [CFTALKTor] searching 65 million records with MSSQL >> >> >> Bob and Terry, >> >> Thanks for the quick reply. "Crapping-out" was actually my >> technical term >> :-) At 12:30am I just couldn't think straight. Apparently the >> query was >> timing-out. The server is a Dell P450-ish running NT4 and I >> would assume >> MSSQL7. As, mssql would do fine if searching on just one or >> two columns with >> no OR. As soon as the first OR was added to the WHERE, things >> began grinding >> to a halt and on additional ORs, the time-outs began. >> >> The idea of breaking everything into chunks was suggested by >> another person. >> I figured mssql shouldn't need to do that but since I have yet to know >> "everything" I figured I'd ask :-) >> >> I'll try to get more detailed info, as well as the sql >> statement being used, >> within a day or two and post it to the list. >> >> Thanks again for the insight. >> >> Karl >> >>> From: [EMAIL PROTECTED] >>> Reply-To: [EMAIL PROTECTED] >>> Date: Wed, 12 Jun 2002 07:16:18 -0400 >>> To: <[EMAIL PROTECTED]> >>> Subject: RE: [CFTALKTor] searching 65 million records with MSSQL >>> >>> If your table is that big, don't use MS-SQL. If he didn't >> like MS products, >>> perhaps he would know a more concise term then "crapping out". >>> >>> Searching in "chunks" of records will probably cost you >> MORE cpu time, not >>> less. >>> >>> If there is no index on the fields in question, apply >> relevant indexes to >>> make the select faster. >>> >>> If there are indexes, the "OR" statement may be preventing >> the usage of an >>> index. Try using a UNION across 2 separate queries. >>> >>> Terry Fielder >>> Network Engineer >>> Great Gulf Homes / Ashton Woods Homes >>> [EMAIL PROTECTED] >>> >>>> -----Original Message----- >>>> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED]]On >>>> Behalf Of A. Karl Zarudny >>>> Sent: Wednesday, June 12, 2002 12:30 AM >>>> To: [EMAIL PROTECTED] >>>> Subject: [CFTALKTor] searching 65 million records with MSSQL >>>> >>>> >>>> Hi everybody. >>>> >>>> Hopefully one of you can provide some insight to this one. I >>>> recently met >>>> someone who said they were having trouble with MSSQL >>>> crapping-out (that's a >>>> technical term) when querying a 65 million record db. Their >>>> SQL statement >>>> which they were apparently trying to run from Query Analyser, >>>> was something >>>> like.... >>>> >>>> select >>>> acolumn, >>>> bcolumn, >>>> ccolumn >>>> from >>>> tablename >>>> where >>>> (acolumn = 'boo' AND bcolumn = 'yikes') OR >>>> (bcolumn = 'haha' AND ccolumn = 'clown') >>>> >>>> >>>> I'll try to get the exact query they were trying to execute. >>>> However any >>>> thoughts at this point? Is 65 million records more than MSSQL >>>> can handle? >>>> Any suggested workarounds?.... perhaps search in chunks of >> records as >>>> opposed to all of them? >>>> >>>> Thanks, >>>> Karl >>>> >>>> - >>>> You are subscribed to the CFUGToronto CFTALK ListSRV. >>>> This message has been posted by: "A. Karl Zarudny" >>>> <[EMAIL PROTECTED]> >>>> To Unsubscribe, Please Visit and Login to > http://www.CFUGToronto.org/ >>> Manager: Kevin Towes ([EMAIL PROTECTED]) >> http://www.CFUGToronto.org/ >> This System has been donated by Infopreneur, Inc. >> (http://www.infopreneur.net) >> >> - >> You are subscribed to the CFUGToronto CFTALK ListSRV. >> This message has been posted by: [EMAIL PROTECTED] >> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ >> Manager: Kevin Towes ([EMAIL PROTECTED]) > http://www.CFUGToronto.org/ >> This System has been donated by Infopreneur, Inc. >> (http://www.infopreneur.net) > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: "A. Karl Zarudny" > <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: [EMAIL PROTECTED] > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "A. Karl Zarudny" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
