How to make the database execute query faster?

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?Optimize the schema, the query, and then some.
Impossible to say without seeing DDL and sample data. A general description
of the server might also help.
Read this for clues:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/

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

how to make query run faster?

hello i have two queries that i need to combine.... the first queryexecutes within one second and the second query executes within 7 seconds.... I used UNION to join this two query.. but my problem is that when using UNION my query now executes within 3:00 minutes?... how can i make my query run faster?... thanks so much....

here's my query:

SELECT d.question_section_name, d.question_sort_order, d.question_number,
d.question_text, d.Range, d.answer, d.Points, d.audit_id, d.question_type,
d.weighted, d.na, d.pf, d.nw, d.wh_survey_id, d.wh_question_id,
d.audit_date, d.survey_name, d.user_name, d.user_email, d.location_id,
b.question_section_name as question_section_name_2, b.rating,
b.survey_threshold_success, b.survey_threshold_failure, b.survey_threshold,
b.maximum_points, b.scored_points, b.percent_scored_of_max,
b.percent_total_score
FROM vw_audit_detail_report d JOIN (SELECT TOP 1 * FROM
vw_audit_detail_report_summary e
WHERE (e.location_id = 4919
AND (e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))) b
ON d.audit_id = b.audit_id and (d.question_section_name <>
b.question_section_name)
WHERE (d.location_id = 4919
AND (d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
And d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f where
(f.location_id = 4919
AND (f.audit_date BETWEEN '2003-07-11' AND '2003-07-11')))

UNION

SELECT a.question_section_name, a.question_sort_order, a.question_number,
a.question_text, a.Range, a.answer, a.Points, a.audit_id, a.question_type,
a.weighted, a.na, a.pf, a.nw, a.wh_survey_id, a.wh_question_id,
a.audit_date, a.survey_name, a.user_name, a.user_email, a.location_id,
c.question_section_name as question_section_name_2, c.rating,
c.survey_threshold_success, c.survey_threshold_failure, c.survey_threshold,
c.maximum_points, c.scored_points, c.percent_scored_of_max,
c.percent_total_score
FROM vw_audit_detail_report a
INNER JOIN vw_audit_detail_report_summary c ON a.audit_id = c.audit_id AND
a.question_section_name = c.question_section_name
WHERE (a.location_id = 4919
AND (a.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))

What indexes do you have on the underlying tables?

|||If you are doing this in a stored procedure you might also want to consider dumping the first to a #temp table, and then inserting the 2nd into the same table, with a 3rd query to pull the data out of the #temp table. Once the procedure finishes the #temp table will disappear. Also as the other person suggested, look at your indexes. Put this query in query analyzer and get the execution plan to see where some of the backup is coming from. I see many of your joins are to views as well. Unless they are sorted and indexed as well, that can cause some issue. for something this complicated I usually try to get everything I can directly from the tables involved. views on views on views is never a good thing, and extremely difficult to diagnose!

my 2 cents

|||

A couple of comments on the first query: (NOTE: this may not make any difference...)

The code marked with Blue seems overly restrictive. It will only return rows that occurred at EXACTLY midnight on July 11, 2003. Are you sure you didn't mean to include all rows throughout the date of July 11th?

The code marked with Yellow seems unneeded and 'may' contribute to a slow response. You are doing a JOIN with a subset of data that is already constrained by the same criteria. Does this add anything? Is it possible that the Audit_ID values in either table have would have changed for that date range?

The code marked with Orange seems unneeded. The JOIN conditions with vw_audit_detail_report_summary (derived table 'b') seem to have previous precluded question_section_name being in both tables.

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
WHERE ( ( d.location_id = 4919
AND d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
AND d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f
WHERE ( f.location_id = 4919
AND f.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
)
)

Unless I'm really misreading this (and on Monday, anything is possible), it seems like the query could be revised as:

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)

And then you do a UNION with the second query, so it seems that the

AND d.question_section_name <> b.question_section_name

and the entire second query, could also be eliminated ...

|||thanks so much.... that solved my problem...

how to make query run faster?

hello i have two queries that i need to combine.... the first queryexecutes within one second and the second query executes within 7 seconds.... I used UNION to join this two query.. but my problem is that when using UNION my query now executes within 3:00 minutes?... how can i make my query run faster?... thanks so much....

here's my query:

SELECT d.question_section_name, d.question_sort_order, d.question_number,
d.question_text, d.Range, d.answer, d.Points, d.audit_id, d.question_type,
d.weighted, d.na, d.pf, d.nw, d.wh_survey_id, d.wh_question_id,
d.audit_date, d.survey_name, d.user_name, d.user_email, d.location_id,
b.question_section_name as question_section_name_2, b.rating,
b.survey_threshold_success, b.survey_threshold_failure, b.survey_threshold,
b.maximum_points, b.scored_points, b.percent_scored_of_max,
b.percent_total_score
FROM vw_audit_detail_report d JOIN (SELECT TOP 1 * FROM
vw_audit_detail_report_summary e
WHERE (e.location_id = 4919
AND (e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))) b
ON d.audit_id = b.audit_id and (d.question_section_name <>
b.question_section_name)
WHERE (d.location_id = 4919
AND (d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
And d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f where
(f.location_id = 4919
AND (f.audit_date BETWEEN '2003-07-11' AND '2003-07-11')))

UNION

SELECT a.question_section_name, a.question_sort_order, a.question_number,
a.question_text, a.Range, a.answer, a.Points, a.audit_id, a.question_type,
a.weighted, a.na, a.pf, a.nw, a.wh_survey_id, a.wh_question_id,
a.audit_date, a.survey_name, a.user_name, a.user_email, a.location_id,
c.question_section_name as question_section_name_2, c.rating,
c.survey_threshold_success, c.survey_threshold_failure, c.survey_threshold,
c.maximum_points, c.scored_points, c.percent_scored_of_max,
c.percent_total_score
FROM vw_audit_detail_report a
INNER JOIN vw_audit_detail_report_summary c ON a.audit_id = c.audit_id AND
a.question_section_name = c.question_section_name
WHERE (a.location_id = 4919
AND (a.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))

What indexes do you have on the underlying tables?

|||If you are doing this in a stored procedure you might also want to consider dumping the first to a #temp table, and then inserting the 2nd into the same table, with a 3rd query to pull the data out of the #temp table. Once the procedure finishes the #temp table will disappear. Also as the other person suggested, look at your indexes. Put this query in query analyzer and get the execution plan to see where some of the backup is coming from. I see many of your joins are to views as well. Unless they are sorted and indexed as well, that can cause some issue. for something this complicated I usually try to get everything I can directly from the tables involved. views on views on views is never a good thing, and extremely difficult to diagnose!

my 2 cents

|||

A couple of comments on the first query: (NOTE: this may not make any difference...)

The code marked with Blue seems overly restrictive. It will only return rows that occurred at EXACTLY midnight on July 11, 2003. Are you sure you didn't mean to include all rows throughout the date of July 11th?

The code marked with Yellow seems unneeded and 'may' contribute to a slow response. You are doing a JOIN with a subset of data that is already constrained by the same criteria. Does this add anything? Is it possible that the Audit_ID values in either table have would have changed for that date range?

The code marked with Orange seems unneeded. The JOIN conditions with vw_audit_detail_report_summary (derived table 'b') seem to have previous precluded question_section_name being in both tables.

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
WHERE ( ( d.location_id = 4919
AND d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
AND d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f
WHERE ( f.location_id = 4919
AND f.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
)
)

Unless I'm really misreading this (and on Monday, anything is possible), it seems like the query could be revised as:

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)

And then you do a UNION with the second query, so it seems that the

AND d.question_section_name <> b.question_section_name

and the entire second query, could also be eliminated ...

|||thanks so much.... that solved my problem...