To clarify Terry's issue, in MSSQL simply saying UNION will eliminate duplicates. To keep duplicates you use UNION ALL.
Getting back to Karl's error, a time-out isn't really an error. The MSSQL database engine doesn't have a time-out built in. You could theoretically have a query that runs for days or weeks without timing out. The time-out interval is set at the client. For Query Analyzer (QA), go to Tools -> Options -> Connections and check the value for Query time-out. That's using the version of QA that ships with MSSQL2K, it might be slightly different for MSSQL7, but I recall that its still under Tools -> Options. Bob ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, June 13, 2002 2:37 AM 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: "Bob Silverberg" <[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)
