List:General Discussion« Previous MessageNext Message »
From:christopher.l.hood Date:July 28 2004 12:25pm
Subject:Indexed Database still slow
View as plain text  
Ok, I will be the first to say that I am learning about indexes, however
it is my understanding that if I have a database with MANY rows and I
wish my queries to be faster I should index my database. With that being
said, I have 2 tables in my database that are being queried with a
single query using a UNION these 2 tables combined are about 9 Million
records (yes I said million). 
 
My query which is below takes about 1 minute to run, now some people
would say that this isn't long, however when the 2 tables were sub 5
million it only took a matter of about 20 seconds to run, so I figure I
need an index. So I have created an index called "Main" within both
tables and added 6 columns to that index, most of the columns that are
used in my query.
 
Sorry for the long background, but here is the problem, my query DID NOT
speed up at all. It still takes right at 1 minute per query, so indexing
didn't buy me anything as far as I can tell.
 
Can someone tell me how the indexes are supposed to be done ( to ensure
that I did it correctly) and tell me if they think that it should have
sped up or if there is a more efficient way to do my query.
 
 
###QUERY HERE ###
 
Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time,
PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name,
PRTC_DIALUP.Framed_IP_Address 
from PRTC_DIALUP 
Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND
(PRTC_DIALUP.Date = 'one-date-here' OR PRTC_DIALUP.Date =
'one-day-earlier' OR PRTC_DIALUP.Date = 'one-day-later') 
UNION 
Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time,
PRTC_DSL.Record_Type,  PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address 
from PRTC_DSL 
Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and (PRTC_DSL.Date =
'one-date-here' OR PRTC_DSL.Date = 'one-day-earlier' OR PRTC_DSL.Date =
'one-day-later') 
order by Full_Name, Time;
 
### END QUERY ###
 
Chris Hood  
Investigator Verizon Global Security Operations Center 
Email:  <mailto:christopher.l.hood@stripped>
christopher.l.hood@stripped 
Desk: 972.399.5900        

Verizon Proprietary 

NOTICE - This message and any attached files may contain information
that is confidential and/or subject of legal privilege intended only for
the use by the intended recipient.  If you are not the intended
recipient or the person responsible for delivering the message to the
intended recipient, be advised that you have received this message in
error and that any dissemination, copying or use of this message or
attachment is strictly forbidden, as is the disclosure of the
information therein.  If you have received this message in error please
notify the sender immediately and delete the message. 
 

Thread
Indexed Database still slowchristopher.l.hood28 Jul
  • Re: Indexed Database still slowBrent Baisley28 Jul
    • Re: Indexed Database still slowSGreen28 Jul
  • Re: Indexed Database still slowSGreen28 Jul
    • Re: Indexed Database still slowDan Nelson28 Jul
  • Re: Indexed Database still slowEgor Egorov29 Jul
RE: Indexed Database still slowchristopher.l.hood28 Jul