How to make the database execute query faster PLEASE HELP

i got a very big problem help i need my database to execute one query in les
s
than a min but it executes for 8 mins is there any way i can shorten the
period of time execution?
this is the codei am excuting some of the tables have 1-3gb big in size
SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
b125_SNPContigLoc_35_1.phys_pos,b125_SNPContigLoc_35_1.rc_ngbr,
b125_SNPContigLoc_35_1.allele,
b125_SNPContigLocusId_35_1.contig_acc,SNPSubSNPLink.subsnp_id,
SubSNPSeq3_ins.line_num,SubSNPSeq3_ins.line3,SubSNPSeq5_ins.line_num,
SubSNPSeq5_ins.line5,b125_ContigInfo_35_1.group_term,
b125_ContigInfo_35_1.group_label,b125_ContigInfo_35_1.contig_label,
AlleleFreqBySsPop.source,AlleleFreqBySsPop.freq,
SubPop.samplesize
From tx
INNER JOIN OmimVarLocusIdSNP
ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
INNER JOIN Gene
ON tx.Omim_No=Gene.Omim_No
INNER JOIN b125_SNPContigLoc_35_1
ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLoc_35_1.snp_id
INNER JOIN b125_SNPContigLocusId_35_1
ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLocusId_35_1.snp_id
INNER JOIN SNPSubSNPLink
ON OmimVarLocusIdSNP.snp_id=SNPSubSNPLink.snp_id
INNER JOIN AlleleFreqBySsPop
ON SNPSubSNPLink.subsnp_id=AlleleFreqBySsPop.subsnp_id
INNER JOIN SubPop
ON SNPSubSNPLink.subsnp_id=SubPop.subsnp_id
INNER JOIN SubSNPSeq3_ins
ON SNPSubSNPLink.subsnp_id=SubSNPSeq3_ins.subsnp_id
INNER JOIN SubSNPSeq5_ins
ON SNPSubSNPLink.subsnp_id=SubSNPSeq5_ins.subsnp_id
INNER JOIN b125_ContigInfo_35_1
ON b125_SNPContigLocusId_35_1.contig_acc=b125_ContigInfo_35_1.contig_acc
WHERE
tx.Omim_Text LIKE '%LIVER%'
ORDER BY snp_id ASCConsider using full-text search (FTS). What data type is the Omim_Text
column? If it's one of the larger types (text, ntext, varchar(>900)) using
FTS will improve execution of such queries.
ML
http://milambda.blogspot.com/|||Apart from database tuning, have you set up and tuned your server
aappropriately? Are databases on a seperate drive from log files? Are
they seperate from temp directories? Do you have enough memeory and
CPU?
While it's true that most queries are sluggish becuase of the way that
they are written, a finely tunned server can add a lot to the
performance.
What's the bottlenexck in this particular query? Is it the DISTINCT,
or is it not taking advantage of indexes?|||I think its because of the like operator.
Just to make sure.
Can you run this query and find out how long it takes?
select * from
tx.Omim_Text LIKE '%LIVER%'
and what is this table tx. how many rows does it have?
And just a comment on ML's post. Fulltext indexing will help if number of
rows is in the order of a few millions and moreover, since the index is
seperately stored outside SQL, it will use lots of I/O in the process. So I
would suggest we go for FTS only if all else fails.|||out of interest, how long does:
SELECT * FROM tx WHERE Omim_text LIKE '%LIVER%' take to return, and how
many rows do you get back?
if this is the bottleneck then as ML said, full-text search could help.
Otherwise it looks like indexes on your joins would be helpful.
If only there was a way to post an execution plan...|||Firstly, have you checked the execution plan to make sure the tables
are procesed in the order you expect and the correct indexes have been
used?
Secondly, how big is the tx table? Is the fact that it's doing a table
scan on this table the reason it's slow? Or is it joining onto one of
the other tables that's slow?
As other posters have said, simplify the query to just "select ...
from tx where ..." to see if that's slow. If it is then there's
probably not a lot you can do (apart from maybe a full-text search,
however I have never done this myself). If that's fast then keep adding
tables to the query until you can pinpoint when it slows down.
Kris|||Include DDL, indexes, primary keys and foreign keys, a description of the
business issue (do you really need all these tables?), and how many rows you
actually expect to be returned.
"Fairy239" <Fairy239@.discussions.microsoft.com> wrote in message
news:D38E93C4-8C58-443B-BDD1-034F1C783B12@.microsoft.com...
> i got a very big problem help i need my database to execute one query in
less
> than a min but it executes for 8 mins is there any way i can shorten the
> period of time execution?
> this is the codei am excuting some of the tables have 1-3gb big in size
> SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
> b125_SNPContigLoc_35_1.phys_pos,b125_SNPContigLoc_35_1.rc_ngbr,
> b125_SNPContigLoc_35_1.allele,
> b125_SNPContigLocusId_35_1.contig_acc,SNPSubSNPLink.subsnp_id,
> SubSNPSeq3_ins.line_num,SubSNPSeq3_ins.line3,SubSNPSeq5_ins.line_num,
> SubSNPSeq5_ins.line5,b125_ContigInfo_35_1.group_term,
> b125_ContigInfo_35_1.group_label,b125_ContigInfo_35_1.contig_label,
> AlleleFreqBySsPop.source,AlleleFreqBySsPop.freq,
> SubPop.samplesize
>
> From tx
> INNER JOIN OmimVarLocusIdSNP
> ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
> INNER JOIN Gene
> ON tx.Omim_No=Gene.Omim_No
> INNER JOIN b125_SNPContigLoc_35_1
> ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLoc_35_1.snp_id
> INNER JOIN b125_SNPContigLocusId_35_1
> ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLocusId_35_1.snp_id
> INNER JOIN SNPSubSNPLink
> ON OmimVarLocusIdSNP.snp_id=SNPSubSNPLink.snp_id
> INNER JOIN AlleleFreqBySsPop
> ON SNPSubSNPLink.subsnp_id=AlleleFreqBySsPop.subsnp_id
> INNER JOIN SubPop
> ON SNPSubSNPLink.subsnp_id=SubPop.subsnp_id
> INNER JOIN SubSNPSeq3_ins
> ON SNPSubSNPLink.subsnp_id=SubSNPSeq3_ins.subsnp_id
> INNER JOIN SubSNPSeq5_ins
> ON SNPSubSNPLink.subsnp_id=SubSNPSeq5_ins.subsnp_id
> INNER JOIN b125_ContigInfo_35_1
> ON b125_SNPContigLocusId_35_1.contig_acc=b125_ContigInfo_35_1.contig_acc
> WHERE
> tx.Omim_Text LIKE '%LIVER%'
>
> ORDER BY snp_id ASC
>|||I need a total a total of 17columns btw i am using the sql 2005 version.
ifthere is any way to tune the sql please teach me how to
.
tx=56.6mb ,b125_SNPContigLoc_35_1=2.91gb,
b125_SNPContigLocusId_35_1=854mb,SNPSubS
NPLink=147mb,
SubSNPSeq3_ins=1.87gb,SubSNPSeq5_ins=1.87gb,
b125_ContigInfo_35_1=590kb,AlleleFreqByS
sPop=899mb,SubPop=117mb
I feel it is the join of the tables that makes it slow as the files are very
big
i tried doins the first 2 columns they took me
i tried doing this and it took me 16sec.
SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
From tx
INNER JOIN OmimVarLocusIdSNP
ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
INNER JOIN Gene
ON tx.Omim_No=Gene.Omim_No
WHERE
tx.Omim_Text LIKE '%LIVER%'
ORDER BY snp_id ASC
----
--
but when i add one table and three columns it took me 2min24sec
SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
b125_SNPContigLoc_35_1.phys_pos,b125_SNPContigLoc_35_1.rc_ngbr,
b125_SNPContigLoc_35_1.allele,
From tx
INNER JOIN OmimVarLocusIdSNP
ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
INNER JOIN Gene
ON tx.Omim_No=Gene.Omim_No
INNER JOIN b125_SNPContigLoc_35_1
ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLoc_35_1.snp_id
WHERE
tx.Omim_Text LIKE '%LIVER%'
ORDER BY snp_id ASC
"Jim Underwood" wrote:

> Include DDL, indexes, primary keys and foreign keys, a description of the
> business issue (do you really need all these tables?), and how many rows y
ou
> actually expect to be returned.
>
> "Fairy239" <Fairy239@.discussions.microsoft.com> wrote in message
> news:D38E93C4-8C58-443B-BDD1-034F1C783B12@.microsoft.com...
> less
>
>|||The data type is text .Omim_text is one column has a lot of words
"ML" wrote:

> Consider using full-text search (FTS). What data type is the Omim_Text
> column? If it's one of the larger types (text, ntext, varchar(>900)) using
> FTS will improve execution of such queries.
>
> ML
> --
> http://milambda.blogspot.com/|||Fairy239 wrote:
> I feel it is the join of the tables that makes it slow as the files are ve
ry
> big
Try turning FORCEPLAN on to force the optimiser to process the tables
in order you have them in the query (note: make sure you have the order
correct first!). And possibly you could force indexes.
e.g.
SET FORCEPLAN ON
SELECT DISTINCT OmimVarLocusIdSNP.snp_id,Gene.Gene_Name,
b125_SNPContigLoc_35_1.phys_pos,b125_SNPContigLoc_35_1.rc_ngbr,
b125_SNPContigLoc_35_1.allele,
>From tx
INNER JOIN OmimVarLocusIdSNP WITH (INDEX(xxxxxxx))
ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
INNER JOIN Gene WITH (INDEX(xxxxxxx))
ON tx.Omim_No=Gene.Omim_No
INNER JOIN b125_SNPContigLoc_35_1 WITH (INDEX(xxxxxxx))
ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLoc_35_1.snp_id
WHERE tx.Omim_Text LIKE '%LIVER%'
ORDER BY snp_id ASC
SET FORCEPLAN OFF
Others will tell you that you shouldn't have to use FORCEPLAN but in my
experience the optimiser often gets it wrong for queries with large
tables.
Kris