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 Application.LoadPackage() and Package.Execute() to run asynchroniously?

Hi,

I am trying to execute a SSIS package programmatically. When a user drops a file in a shared folder, we execute the package based on that file. I am using SqlServer.DTs.Runtime.Application.LoadPackage() and SqlServer.DTs.Runtime.Package.Execute() functions each time to do this.

The problem is, when, say two people drop a file, the second one will not execute untill the first one is completed. I also tried only calling LoadPackage() a single time, and then storing the instance and calling Execute() in a different thread on each file drop; although the blocking still occurs. I assume the Package object is the one doing the blocking then behind the scenes.

Is there any built in functionality to make these functions (Application.LoadPackage() and Package.Execute()) run async? If not, has anyone had much success sticking these calls into a thread? I tried sticking these calls into a thread using System.Threading.ThreadPool.QueueUserWorkItem(), and this appears to work, however it randomly crashes the program when I drop multiple files one after the other. The exception also isn't too helpfull (pasted below):

"The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails."

So, is what I am trying to do possible? I know it must be, because when I was using .NET 1.X, I was just calling dtexec via the command line, and dtexec was able to execute many packages simultaneously....

Thanks for any help,

DrewThe calls are synchronous, but each package object is independent - so if you create a thread per incoming file, create a new package object and do LoadPackage/Execute you should get the behavior you need.

The random errors you see are probably caused by your code trying to load the DTSX file before it was fully copied. So SSIS tries to read partial file and throws the error reporting it is not a valid XML file. You need some way to ensure you only load a file when it is ready, e.g. try opening it exclusively until you succeed (it will fail if someone is still writing to the file).

how to maintain database concurrence?

My application uses sql for performing operation. something like conncetion.execute(query). so there is only conncetion object no recordset object or something like that .

i want to run multiple instence of my application so i want to maintain integrety of data. and i am looking for solution through sql for locking mechanism. so concurrent data access dont currept data.

I hope you have uderstand my requirement.Avoid locking whenever possible! Use optimistic concurrency (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcoptimistic_concurrency.asp) instead of relying on locking.

-PatP

How to let general user use xp_cmdshell?

Hi,

Is any way can let user (not admin role) use xp_cmdshell?

I write a SP and include xp_cmdshell to execute .exe file.

So I hope user can execute xp_cmdshell, too!

Any idea?

Thanks!

First, why would you want a user running xp_cmdshell? Talk about an open entry to your servers. Does your system admin know about this?

Second, not 100% but I think SP2 eliminated the ability for processes to run xp_cmdshell.

How to know a Job Status from Stored Procedure

To execute a job from VB, I use stored procedure with T-SQL :

EXEC sp_start_job @.job_name = 'DTSName'

>From BOL, I know that procedure return 0 (success) or 1 (failure), but
that's only to tell user that job successfully started, right?
What I need is how to know is that job have already executed
successfully or
failed, so if success i will execute another code.

Please help me, thanksyou might want your DTS package to tell you when it fails or not and
based on that execute your next step of code. just a thought.

How to know a Job Status from Stored Procedure

To execute a job from VB, I use stored procedure with T-SQL :

EXEC sp_start_job @.job_name = 'DTSName'

>From BOL, I know that procedure return 0 (success) or 1 (failure), but
that's only to tell user that job successfully started, right?
What I need is how to know is that job have executed successfully or
failed, so if success i will execute another code.

Please help me, thanksSee sp_help_job in Books Online. Alternatively, you could use SQL-DMO
from VB to get the CurrentRunStatus and/or LastRunOutcome property of
the Job object. Another option would be to add more steps to the job
itself, so that if the first step fails, it runs your extra code.

Simon|||But when I have execute sp_help_job if i don't know the job was
finished or not?|||The current_execution_status column tells you if the job is running;
the last_run_outcome columns tells you what happened the last time the
job finished.

If you want something to happen only when the job succeeds (or fails),
then it might be easiest to add another job step, as I mentioned. Then
you don't have to keep checking the job status from the client.

Simon