How to make the database execute query faster PLEASE HELP

microsoft.public.sqlserver.programming

4 May 2006 9:52 AM

Fairy239

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

4 May 2006 10:13 AM

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/

4 May 2006 10:40 AM

Will

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...

5 May 2006 1:33 AM

Fairy239

The data type is text .Omim_text is one column has a lot of words

Show quote

"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/

4 May 2006 10:32 AM

Stu

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?

4 May 2006 10:39 AM

Omnibuzz

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.

4 May 2006 11:12 AM

kriskirk

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

4 May 2006 1:12 PM

Jim Underwood

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.

Show quote

"Fairy239" <Fairy***@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
>
>

5 May 2006 1:28 AM

Fairy239

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,SNPSubSNPLink=147mb,
SubSNPSeq3_ins=1.87gb,SubSNPSeq5_ins=1.87gb,
b125_ContigInfo_35_1=590kb,AlleleFreqBySsPop=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

Show quote

"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 you
> actually expect to be returned.
>
>
> "Fairy239" <Fairy***@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
> >
> >
>
>
>

5 May 2006 1:51 AM

kriskirk

Fairy239 wrote:

> I feel it is the join of the tables that makes it slow as the files are very
> 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

5 May 2006 2:55 AM

Fairy239

how do i set the index i got this error after replacing the xx with 1,2,3
Msg 307, Level 16, State 1, Line 4
Index ID 1 on table 'OmimVarLocusIdSNP' (specified in the FROM clause) does
not exist.

Show quote

"krisk***@hotmail.com" wrote:

> Fairy239 wrote:
> > I feel it is the join of the tables that makes it slow as the files are very
> > 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
>
>

5 May 2006 4:15 AM

kriskirk

Fairy239 wrote:

> how do i set the index i got this error after replacing the xx with 1,2,3
> Msg 307, Level 16, State 1, Line 4
> Index ID 1 on table 'OmimVarLocusIdSNP' (specified in the FROM clause) does
> not exist.

Use the index name rather than ID.

e.g.
SELECT .... FROM table WITH (INDEX(idx_KeyCol))

Kris

5 May 2006 6:27 AM

Fairy239

i did this
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(1_Omim_no))
ON tx.Omim_No=OmimVarLocusIdSNP.omim_id
INNER JOIN Gene WITH (INDEX(2_Omim_no))
ON tx.Omim_No=Gene.Omim_No
INNER JOIN b125_SNPContigLoc_35_1 WITH (INDEX(3_snp_id))
ON OmimVarLocusIdSNP.snp_id=b125_SNPContigLoc_35_1.snp_id
WHERE tx.Omim_Text LIKE '%LIVER%'
ORDER BY snp_id ASC

SET FORCEPLAN OFF

and got this error

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '1_Omim_no'.
Msg 319, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.
Msg 319, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.

Show quote

"krisk***@hotmail.com" wrote:

> Fairy239 wrote:
> > how do i set the index i got this error after replacing the xx with 1,2,3
> > Msg 307, Level 16, State 1, Line 4
> > Index ID 1 on table 'OmimVarLocusIdSNP' (specified in the FROM clause) does
> > not exist.
>
> Use the index name rather than ID.
>
> e.g.
> SELECT .... FROM table WITH (INDEX(idx_KeyCol))
>
> Kris
>
>

5 May 2006 8:02 AM

kriskirk

It looks okay to me. Those error messages look strange though - are you
executing this via Query Analyser?

Kris

5 May 2006 8:28 AM

Will

the error messages might be because you're beginning your index names
with numbers, I've always found I need to wrap things up in [] if I
prefix with a number - so I don't do it.

Rather than going down this slightly rigid route of forcing the query
plan to do exactly what we think it should, can we take a step back and
check that we've got all the right things in place for it to do things
itself:

is there an index on b125_SNPContigLoc_35_1.snp_id ?

what is the main cost in the execution plan when this query is run
(switch on execution plans using CTRL-M in management studio). It's
possible that you've actually got something like an expensive bookmark
lookup, in which case we can force all the indexes we like, it won't
help.

if you created an index on these columns with something like:

CREATE INDEX IX_snpContigLoc_Covering ON
b125_SNPContigLoc_35_1(phys_pos, rc_ngbr, allele, snp_id)

does it speed up the query (if not drop the index again).

I get the feeling that the route we're taking may help initially, but
in a few weeks they'll be a new problem.

Cheers
Will

5 May 2006 12:54 PM

Jim Underwood

Most importantly we need DDL so that we are not guessing at the indexes. I
should have posted this link the first time around. Take a look at the link
then post DDL so we know what your datatypes are and what your indexes are.
Also, how many rows is this SQL returning? Is it returning 2 rows in 8
minutes, or 12 million rows in 8 minutes?

Here is the link:
http://www.aspfaq.com/etiquette.asp?id=5006

Show quote

"Will" <william_p***@yahoo.co.uk> wrote in message
news:1146817719.377174.25740@j33g2000cwa.googlegroups.com...
> the error messages might be because you're beginning your index names
> with numbers, I've always found I need to wrap things up in [] if I
> prefix with a number - so I don't do it.
>
> Rather than going down this slightly rigid route of forcing the query
> plan to do exactly what we think it should, can we take a step back and
> check that we've got all the right things in place for it to do things
> itself:
>
> is there an index on b125_SNPContigLoc_35_1.snp_id ?
>
> what is the main cost in the execution plan when this query is run
> (switch on execution plans using CTRL-M in management studio). It's
> possible that you've actually got something like an expensive bookmark
> lookup, in which case we can force all the indexes we like, it won't
> help.
>
> if you created an index on these columns with something like:
>
> CREATE INDEX IX_snpContigLoc_Covering ON
> b125_SNPContigLoc_35_1(phys_pos, rc_ngbr, allele, snp_id)
>
> does it speed up the query (if not drop the index again).
>
> I get the feeling that the route we're taking may help initially, but
> in a few weeks they'll be a new problem.
>
> Cheers
> Will
>

8 May 2006 1:17 AM

Fairy239

this is the ddl i don't understand how to make the link i using verion 2005
i need 17 columns outputed out.

GO
/****** Object: Table [dbo].[AlleleFreqBySsPop]    Script Date: 05/08/2006
09:03:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AlleleFreqBySsPop](
    [subsnp_id] [int] NOT NULL,
    [pop_id] [int] NOT NULL,
    [allele_id] [int] NOT NULL,
    [source] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [cnt] [real] NOT NULL,
    [freq] [real] NOT NULL,
    [last_updated_time] [datetime] NOT NULL
) ON [PRIMARY]
rows around 19mil
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[b125_SNPContigLoc_35_1]    Script Date:
05/08/2006 09:02:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[b125_SNPContigLoc_35_1](
    [snp_type] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [snp_id] [int] NOT NULL,
    [ctg_id] [int] NOT NULL,
    [asn_from] [int] NOT NULL,
    [asn_to] [int] NOT NULL,
    [lf_ngbr] [int] NOT NULL,
    [rf_ngbr] [int] NOT NULL,
    [lc_ngbr] [int] NOT NULL,
    [rc_ngbr] [int] NOT NULL,
    [loc_type] [tinyint] NOT NULL,
    [phys_pos_from] [int] NULL,
    [phys_pos] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [snp_bld_id] [int] NOT NULL,
    [last_updated_time] [datetime] NOT NULL,
    [process_status] [tinyint] NOT NULL,
    [orientation] [tinyint] NOT NULL,
    [allele] [varchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [loc_sts_uid] [int] NULL,
    [aln_quality] [float] NULL,
    [num_mism] [int] NULL,
    [num_del] [int] NULL,
    [num_ins] [int] NULL
) ON [PRIMARY]
25mil rows
GO
SET ANSI_PADDING OFF
USE [human]
GO
/****** Object: Table [dbo].[b125_ContigInfo_35_1]    Script Date:
05/08/2006 09:02:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[b125_ContigInfo_35_1](
    [ctg_id] [int] NOT NULL,
    [tax_id] [int] NOT NULL,
    [contig_acc] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [contig_ver] [tinyint] NULL,
    [contig_name] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [contig_chr] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [contig_start] [int] NULL,
    [contig_end] [int] NULL,
    [orient] [tinyint] NULL,
    [contig_gi] [int] NULL,
    [group_term] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [group_label] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [contig_label] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [build_id] [int] NULL,
    [build_ver] [int] NULL,
    [last_updated_time] [datetime] NOT NULL
) ON [PRIMARY]
2000 rows
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[b125_SNPContigLocusId_35_1]    Script Date:
05/08/2006 09:05:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[b125_SNPContigLocusId_35_1](
    [snp_id] [int] NOT NULL,
    [contig_acc] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [contig_ver] [tinyint] NOT NULL,
    [asn_from] [int] NULL,
    [asn_to] [int] NULL,
    [locus_id] [int] NOT NULL,
    [locus_symbol] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [mrna_acc] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [mrna_ver] [tinyint] NULL,
    [protein_acc] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [protein_ver] [tinyint] NULL,
    [fxn_class] [tinyint] NOT NULL,
    [reading_frame] [tinyint] NULL,
    [allele] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [residue] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [aa_position] [int] NULL,
    [build_id] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ctg_id] [int] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
10mil rows
GO
/****** Object: Table [dbo].[Gene]    Script Date: 05/08/2006 09:08:20
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Gene](
    [Omim_No] [int] NULL,
    [Gene_Name] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
2000rows
GO
/****** Object: Table [dbo].[Gene]    Script Date: 05/08/2006 09:08:20
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Gene](
    [Omim_No] [int] NULL,
    [Gene_Name] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
8000rows
GO
/****** Object: Table [dbo].[SubPop]    Script Date: 05/08/2006 09:10:33
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SubPop](
    [batch_id] [int] NOT NULL,
    [subsnp_id] [int] NOT NULL,
    [pop_id] [int] NOT NULL,
    [type] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [samplesize] [int] NOT NULL,
    [submitted_strand_code] [tinyint] NULL,
    [submitted_rs] [int] NULL,
    [allele_flag] [tinyint] NULL,
    [ambiguity_status] [tinyint] NULL,
    [sub_heterozygosity] [real] NULL,
    [est_heterozygosity] [real] NULL,
    [est_het_se_sq] [real] NULL,
    [last_updated_time] [smalldatetime] NOT NULL,
    [observed] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [sub_het_se_sq] [real] NULL,
    [subpop_id] [int] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
5milrows
GO
/****** Object: Table [dbo].[SubSNPSeq3_ins]    Script Date: 05/08/2006
09:11:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SubSNPSeq3_ins](
    [subsnp_id] [int] NOT NULL,
    [type] [tinyint] NOT NULL,
    [line_num] [tinyint] NOT NULL,
    [line3] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
16mil rows
GO
/****** Object: Table [dbo].[SubSNPSeq5_ins]    Script Date: 05/08/2006
09:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SubSNPSeq5_ins](
    [subsnp_id] [int] NOT NULL,
    [type] [tinyint] NOT NULL,
    [line_num] [tinyint] NOT NULL,
    [line5] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
16mil rows

Show quote

"Jim Underwood" wrote:

> Most importantly we need DDL so that we are not guessing at the indexes. I
> should have posted this link the first time around. Take a look at the link
> then post DDL so we know what your datatypes are and what your indexes are.
> Also, how many rows is this SQL returning? Is it returning 2 rows in 8
> minutes, or 12 million rows in 8 minutes?
>
> Here is the link:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
>
> "Will" <william_p***@yahoo.co.uk> wrote in message
> news:1146817719.377174.25740@j33g2000cwa.googlegroups.com...
> > the error messages might be because you're beginning your index names
> > with numbers, I've always found I need to wrap things up in [] if I
> > prefix with a number - so I don't do it.
> >
> > Rather than going down this slightly rigid route of forcing the query
> > plan to do exactly what we think it should, can we take a step back and
> > check that we've got all the right things in place for it to do things
> > itself:
> >
> > is there an index on b125_SNPContigLoc_35_1.snp_id ?
> >
> > what is the main cost in the execution plan when this query is run
> > (switch on execution plans using CTRL-M in management studio). It's
> > possible that you've actually got something like an expensive bookmark
> > lookup, in which case we can force all the indexes we like, it won't
> > help.
> >
> > if you created an index on these columns with something like:
> >
> > CREATE INDEX IX_snpContigLoc_Covering ON
> > b125_SNPContigLoc_35_1(phys_pos, rc_ngbr, allele, snp_id)
> >
> > does it speed up the query (if not drop the index again).
> >
> > I get the feeling that the route we're taking may help initially, but
> > in a few weeks they'll be a new problem.
> >
> > Cheers
> > Will
> >
>
>
>

8 May 2006 12:57 PM

Jim Underwood

You still have not posted any indexes, which is the most important thing if
you are worried about performance. Do any of these tables have keys?

Also, how many rows are you execting to be returned by this query?

With the number of rows you have in these tables, you will be lucky to
return even one row without the proper indexes.

AlleleFreqBySsPop - rows around 19mil
b125_SNPContigLoc_35_1 - 25mil rows
b125_ContigInfo_35_1 - 2000 rows
b125_SNPContigLocusId_35_1 - 10mil rows
Gene - 2000rows ?????
Gene - 8000rows ?????
SubPop - 5milrows
SubSNPSeq3_ins - 16mil rows
SubSNPSeq5_ins - 16mil rows

"Fairy239" <Fairy***@discussions.microsoft.com> wrote in message
news:D81D8B62-390A-400C-A04E-50F65BEBDACC@microsoft.com...
> this is the ddl i don't understand how to make the link i using verion
2005
> i need 17 columns outputed out.
>
> GO
> /****** Object: Table [dbo].[AlleleFreqBySsPop] Script Date:

05/08/2006
Show quote

> 09:03:13 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[AlleleFreqBySsPop](
> [subsnp_id] [int] NOT NULL,
> [pop_id] [int] NOT NULL,
> [allele_id] [int] NOT NULL,
> [source] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [cnt] [real] NOT NULL,
> [freq] [real] NOT NULL,
> [last_updated_time] [datetime] NOT NULL
> ) ON [PRIMARY]
> rows around 19mil
> GO
> SET ANSI_PADDING OFF
> GO
> /****** Object: Table [dbo].[b125_SNPContigLoc_35_1]    Script Date:
> 05/08/2006 09:02:19 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[b125_SNPContigLoc_35_1](
> [snp_type] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [snp_id] [int] NOT NULL,
> [ctg_id] [int] NOT NULL,
> [asn_from] [int] NOT NULL,
> [asn_to] [int] NOT NULL,
> [lf_ngbr] [int] NOT NULL,
> [rf_ngbr] [int] NOT NULL,
> [lc_ngbr] [int] NOT NULL,
> [rc_ngbr] [int] NOT NULL,
> [loc_type] [tinyint] NOT NULL,
> [phys_pos_from] [int] NULL,
> [phys_pos] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [snp_bld_id] [int] NOT NULL,
> [last_updated_time] [datetime] NOT NULL,
> [process_status] [tinyint] NOT NULL,
> [orientation] [tinyint] NOT NULL,
> [allele] [varchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [loc_sts_uid] [int] NULL,
> [aln_quality] [float] NULL,
> [num_mism] [int] NULL,
> [num_del] [int] NULL,
> [num_ins] [int] NULL
> ) ON [PRIMARY]
> 25mil rows
> GO
> SET ANSI_PADDING OFF
> USE [human]
> GO
> /****** Object: Table [dbo].[b125_ContigInfo_35_1]    Script Date:
> 05/08/2006 09:02:43 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[b125_ContigInfo_35_1](
> [ctg_id] [int] NOT NULL,
> [tax_id] [int] NOT NULL,
> [contig_acc] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [contig_ver] [tinyint] NULL,
> [contig_name] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [contig_chr] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [contig_start] [int] NULL,
> [contig_end] [int] NULL,
> [orient] [tinyint] NULL,
> [contig_gi] [int] NULL,
> [group_term] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [group_label] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [contig_label] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [build_id] [int] NULL,
> [build_ver] [int] NULL,
> [last_updated_time] [datetime] NOT NULL
> ) ON [PRIMARY]
> 2000 rows
> GO
> SET ANSI_PADDING OFF
> GO
> /****** Object: Table [dbo].[b125_SNPContigLocusId_35_1]    Script Date:
> 05/08/2006 09:05:28 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[b125_SNPContigLocusId_35_1](
> [snp_id] [int] NOT NULL,
> [contig_acc] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [contig_ver] [tinyint] NOT NULL,
> [asn_from] [int] NULL,
> [asn_to] [int] NULL,
> [locus_id] [int] NOT NULL,
> [locus_symbol] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [mrna_acc] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [mrna_ver] [tinyint] NULL,
> [protein_acc] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [protein_ver] [tinyint] NULL,
> [fxn_class] [tinyint] NOT NULL,
> [reading_frame] [tinyint] NULL,
> [allele] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [residue] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [aa_position] [int] NULL,
> [build_id] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [ctg_id] [int] NOT NULL
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
> 10mil rows
> GO
> /****** Object: Table [dbo].[Gene]    Script Date: 05/08/2006 09:08:20
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[Gene](
> [Omim_No] [int] NULL,
> [Gene_Name] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
> 2000rows
> GO
> /****** Object: Table [dbo].[Gene]    Script Date: 05/08/2006 09:08:20
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[Gene](
> [Omim_No] [int] NULL,
> [Gene_Name] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
> 8000rows
> GO
> /****** Object: Table [dbo].[SubPop]    Script Date: 05/08/2006 09:10:33
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[SubPop](
> [batch_id] [int] NOT NULL,
> [subsnp_id] [int] NOT NULL,
> [pop_id] [int] NOT NULL,
> [type] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [samplesize] [int] NOT NULL,
> [submitted_strand_code] [tinyint] NULL,
> [submitted_rs] [int] NULL,
> [allele_flag] [tinyint] NULL,
> [ambiguity_status] [tinyint] NULL,
> [sub_heterozygosity] [real] NULL,
> [est_heterozygosity] [real] NULL,
> [est_het_se_sq] [real] NULL,
> [last_updated_time] [smalldatetime] NOT NULL,
> [observed] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [sub_het_se_sq] [real] NULL,
> [subpop_id] [int] NOT NULL
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
> 5milrows
> GO
> /****** Object: Table [dbo].[SubSNPSeq3_ins]    Script Date: 05/08/2006
> 09:11:11 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[SubSNPSeq3_ins](
> [subsnp_id] [int] NOT NULL,
> [type] [tinyint] NOT NULL,
> [line_num] [tinyint] NOT NULL,
> [line3] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
> 16mil rows
> GO
> /****** Object: Table [dbo].[SubSNPSeq5_ins]    Script Date: 05/08/2006
> 09:12:00 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[SubSNPSeq5_ins](
> [subsnp_id] [int] NOT NULL,
> [type] [tinyint] NOT NULL,
> [line_num] [tinyint] NOT NULL,
> [line5] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
> 16mil rows
>
> "Jim Underwood" wrote:
>
> > Most importantly we need DDL so that we are not guessing at the indexes.
I
> > should have posted this link the first time around. Take a look at the
link
> > then post DDL so we know what your datatypes are and what your indexes
are.
> > Also, how many rows is this SQL returning? Is it returning 2 rows in 8
> > minutes, or 12 million rows in 8 minutes?
> >
> > Here is the link:
> > http://www.aspfaq.com/etiquette.asp?id=5006
> >
> >
> > "Will" <william_p***@yahoo.co.uk> wrote in message
> > news:1146817719.377174.25740@j33g2000cwa.googlegroups.com...
> > > the error messages might be because you're beginning your index names
> > > with numbers, I've always found I need to wrap things up in [] if I
> > > prefix with a number - so I don't do it.
> > >
> > > Rather than going down this slightly rigid route of forcing the query
> > > plan to do exactly what we think it should, can we take a step back
and
> > > check that we've got all the right things in place for it to do things
> > > itself:
> > >
> > > is there an index on b125_SNPContigLoc_35_1.snp_id ?
> > >
> > > what is the main cost in the execution plan when this query is run
> > > (switch on execution plans using CTRL-M in management studio). It's
> > > possible that you've actually got something like an expensive bookmark
> > > lookup, in which case we can force all the indexes we like, it won't
> > > help.
> > >
> > > if you created an index on these columns with something like:
> > >
> > > CREATE INDEX IX_snpContigLoc_Covering ON
> > > b125_SNPContigLoc_35_1(phys_pos, rc_ngbr, allele, snp_id)
> > >
> > > does it speed up the query (if not drop the index again).
> > >
> > > I get the feeling that the route we're taking may help initially, but
> > > in a few weeks they'll be a new problem.
> > >
> > > Cheers
> > > Will
> > >
> >
> >
> >

9 May 2006 1:33 AM

Fairy239

now i changed my code to full join it takes even longer i didn't reate and
indexes because it is nessasary i have to tune the database now but i don't
know how to.
please teach me what to do.

Show quote

"Jim Underwood" wrote:

> You still have not posted any indexes, which is the most important thing if
> you are worried about performance. Do any of these tables have keys?
>
> Also, how many rows are you execting to be returned by this query?
>
> With the number of rows you have in these tables, you will be lucky to
> return even one row without the proper indexes.
>
> AlleleFreqBySsPop - rows around 19mil
> b125_SNPContigLoc_35_1 - 25mil rows
> b125_ContigInfo_35_1 - 2000 rows
> b125_SNPContigLocusId_35_1 - 10mil rows
> Gene - 2000rows ?????
> Gene - 8000rows ?????
> SubPop - 5milrows
> SubSNPSeq3_ins - 16mil rows
> SubSNPSeq5_ins - 16mil rows
>
> "Fairy239" <Fairy***@discussions.microsoft.com> wrote in message
> news:D81D8B62-390A-400C-A04E-50F65BEBDACC@microsoft.com...
> > this is the ddl i don't understand how to make the link i using verion
> 2005
> > i need 17 columns outputed out.
> >
> > GO
> > /****** Object: Table [dbo].[AlleleFreqBySsPop]    Script Date:
> 05/08/2006
> > 09:03:13 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[AlleleFreqBySsPop](
> > [subsnp_id] [int] NOT NULL,
> > [pop_id] [int] NOT NULL,
> > [allele_id] [int] NOT NULL,
> > [source] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > [cnt] [real] NOT NULL,
> > [freq] [real] NOT NULL,
> > [last_updated_time] [datetime] NOT NULL
> > ) ON [PRIMARY]
> > rows around 19mil
> > GO
> > SET ANSI_PADDING OFF
> > GO
> > /****** Object: Table [dbo].[b125_SNPContigLoc_35_1]    Script Date:
> > 05/08/2006 09:02:19 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[b125_SNPContigLoc_35_1](
> > [snp_type] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > [snp_id] [int] NOT NULL,
> > [ctg_id] [int] NOT NULL,
> > [asn_from] [int] NOT NULL,
> > [asn_to] [int] NOT NULL,
> > [lf_ngbr] [int] NOT NULL,
> > [rf_ngbr] [int] NOT NULL,
> > [lc_ngbr] [int] NOT NULL,
> > [rc_ngbr] [int] NOT NULL,
> > [loc_type] [tinyint] NOT NULL,
> > [phys_pos_from] [int] NULL,
> > [phys_pos] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [snp_bld_id] [int] NOT NULL,
> > [last_updated_time] [datetime] NOT NULL,
> > [process_status] [tinyint] NOT NULL,
> > [orientation] [tinyint] NOT NULL,
> > [allele] [varchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [loc_sts_uid] [int] NULL,
> > [aln_quality] [float] NULL,
> > [num_mism] [int] NULL,
> > [num_del] [int] NULL,
> > [num_ins] [int] NULL
> > ) ON [PRIMARY]
> > 25mil rows
> > GO
> > SET ANSI_PADDING OFF
> > USE [human]
> > GO
> > /****** Object: Table [dbo].[b125_ContigInfo_35_1]    Script Date:
> > 05/08/2006 09:02:43 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[b125_ContigInfo_35_1](
> > [ctg_id] [int] NOT NULL,
> > [tax_id] [int] NOT NULL,
> > [contig_acc] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > [contig_ver] [tinyint] NULL,
> > [contig_name] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > [contig_chr] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [contig_start] [int] NULL,
> > [contig_end] [int] NULL,
> > [orient] [tinyint] NULL,
> > [contig_gi] [int] NULL,
> > [group_term] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [group_label] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [contig_label] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [build_id] [int] NULL,
> > [build_ver] [int] NULL,
> > [last_updated_time] [datetime] NOT NULL
> > ) ON [PRIMARY]
> > 2000 rows
> > GO
> > SET ANSI_PADDING OFF
> > GO
> > /****** Object: Table [dbo].[b125_SNPContigLocusId_35_1]    Script Date:
> > 05/08/2006 09:05:28 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[b125_SNPContigLocusId_35_1](
> > [snp_id] [int] NOT NULL,
> > [contig_acc] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > [contig_ver] [tinyint] NOT NULL,
> > [asn_from] [int] NULL,
> > [asn_to] [int] NULL,
> > [locus_id] [int] NOT NULL,
> > [locus_symbol] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [mrna_acc] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [mrna_ver] [tinyint] NULL,
> > [protein_acc] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [protein_ver] [tinyint] NULL,
> > [fxn_class] [tinyint] NOT NULL,
> > [reading_frame] [tinyint] NULL,
> > [allele] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [residue] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [aa_position] [int] NULL,
> > [build_id] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > [ctg_id] [int] NOT NULL
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> > 10mil rows
> > GO
> > /****** Object: Table [dbo].[Gene]    Script Date: 05/08/2006 09:08:20
> > ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[Gene](
> > [Omim_No] [int] NULL,
> > [Gene_Name] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> > 2000rows
> > GO
> > /****** Object: Table [dbo].[Gene]    Script Date: 05/08/2006 09:08:20
> > ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[Gene](
> > [Omim_No] [int] NULL,
> > [Gene_Name] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> > 8000rows
> > GO
> > /****** Object: Table [dbo].[SubPop]    Script Date: 05/08/2006 09:10:33
> > ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[SubPop](
> > [batch_id] [int] NOT NULL,
> > [subsnp_id] [int] NOT NULL,
> > [pop_id] [int] NOT NULL,
> > [type] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> > [samplesize] [int] NOT NULL,
> > [submitted_strand_code] [tinyint] NULL,
> > [submitted_rs] [int] NULL,
> > [allele_flag] [tinyint] NULL,
> > [ambiguity_status] [tinyint] NULL,
> > [sub_heterozygosity] [real] NULL,
> > [est_heterozygosity] [real] NULL,
> > [est_het_se_sq] [real] NULL,
> > [last_updated_time] [smalldatetime] NOT NULL,
> > [observed] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [sub_het_se_sq] [real] NULL,
> > [subpop_id] [int] NOT NULL
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> > 5milrows
> > GO
> > /****** Object: Table [dbo].[SubSNPSeq3_ins]    Script Date: 05/08/2006
> > 09:11:11 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[SubSNPSeq3_ins](
> > [subsnp_id] [int] NOT NULL,
> > [type] [tinyint] NOT NULL,
> > [line_num] [tinyint] NOT NULL,
> > [line3] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> > 16mil rows
> > GO
> > /****** Object: Table [dbo].[SubSNPSeq5_ins]    Script Date: 05/08/2006
> > 09:12:00 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[SubSNPSeq5_ins](
> > [subsnp_id] [int] NOT NULL,
> > [type] [tinyint] NOT NULL,
> > [line_num] [tinyint] NOT NULL,
> > [line5] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> > 16mil rows
> >
> > "Jim Underwood" wrote:
> >
> > > Most importantly we need DDL so that we are not guessing at the indexes.
> I
> > > should have posted this link the first time around. Take a look at the
> link
> > > then post DDL so we know what your datatypes are and what your indexes
> are.
> > > Also, how many rows is this SQL returning? Is it returning 2 rows in 8
> > > minutes, or 12 million rows in 8 minutes?
> > >
> > > Here is the link:
> > > http://www.aspfaq.com/etiquette.asp?id=5006
> > >
> > >
> > > "Will" <william_p***@yahoo.co.uk> wrote in message
> > > news:1146817719.377174.25740@j33g2000cwa.googlegroups.com...
> > > > the error messages might be because you're beginning your index names
> > > > with numbers, I've always found I need to wrap things up in [] if I
> > > > prefix with a number - so I don't do it.
> > > >
> > > > Rather than going down this slightly rigid route of forcing the query
> > > > plan to do exactly what we think it should, can we take a step back
> and
> > > > check that we've got all the right things in place for it to do things
> > > > itself:
> > > >
> > > > is there an index on b125_SNPContigLoc_35_1.snp_id ?
> > > >
> > > > what is the main cost in the execution plan when this query is run
> > > > (switch on execution plans using CTRL-M in management studio). It's
> > > > possible that you've actually got something like an expensive bookmark
> > > > lookup, in which case we can force all the indexes we like, it won't
> > > > help.
> > > >
> > > > if you created an index on these columns with something like:
> > > >
> > > > CREATE INDEX IX_snpContigLoc_Covering ON
> > > > b125_SNPContigLoc_35_1(phys_pos, rc_ngbr, allele, snp_id)
> > > >
> > > > does it speed up the query (if not drop the index again).
> > > >
> > > > I get the feeling that the route we're taking may help initially, but
> > > > in a few weeks they'll be a new problem.
> > > >
> > > > Cheers
> > > > Will
> > > >
> > >
> > >

9 May 2006 1:56 AM

Fairy239

How i create a index?i am really lost here. and after i created it what i do?

Show quote

"Fairy239" wrote:

> 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
>
>

9 May 2006 2:04 AM

kriskirk

Fairy239 wrote:

> How i create a index?i am really lost here. and after i created it what i do?

I'm afraid you need to go on a course on basic RDBMS principles before
trying to do anything else. That may seem harshe but if you don't even
know how to create an index and can't look it up in BOL then really you
shouldn't be doing anything to your system.

9 May 2006 3:09 AM

Fairy239

i got the indx out but now the problem how to execute the force plan
i did this
name of my index(IX_OmimVarLocusIdSNP)
Set forceplan on
SELECT Distinct alt_ti.Omim_no,OmimVarLocusIdSNP.snp_id,Gene.Gene_Name
From alt_ti
FULL OUTER JOIN OmimVarLocusIdSNP WITH (INDEX IX_OmimVarLocusIdSNP)
ON alt_ti.Omim_No=OmimVarLocusIdSNP.omim_id
FULL OUTER JOIN Gene
ON alt_ti.Omim_No=Gene.Omim_No
Where
alt_ti.Omim_Alt_Titles Like '%LIVER%'
ORDER BY Omim_No ASC
Set forceplan off

i got this error
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'IX_OmimVarLocusIdSNP'.
what is wrong with it?

Show quote

"krisk***@hotmail.com" wrote:

> Fairy239 wrote:
> > How i create a index?i am really lost here. and after i created it what i do?
>
> I'm afraid you need to go on a course on basic RDBMS principles before
> trying to do anything else. That may seem harshe but if you don't even
> know how to create an index and can't look it up in BOL then really you
> shouldn't be doing anything to your system.
>
>

9 May 2006 3:15 AM

kriskirk

Fairy239 wrote:
Show quote

> i got the indx out but now the problem how to execute the force plan
> i did this
> name of my index(IX_OmimVarLocusIdSNP)
> Set forceplan on
> SELECT Distinct alt_ti.Omim_no,OmimVarLocusIdSNP.snp_id,Gene.Gene_Name
> From alt_ti
> FULL OUTER JOIN OmimVarLocusIdSNP WITH (INDEX IX_OmimVarLocusIdSNP)
> ON alt_ti.Omim_No=OmimVarLocusIdSNP.omim_id
> FULL OUTER JOIN Gene
> ON alt_ti.Omim_No=Gene.Omim_No
> Where
> alt_ti.Omim_Alt_Titles Like '%LIVER%'
> ORDER BY Omim_No ASC
> Set forceplan off
>
> i got this error
> Msg 102, Level 15, State 1, Line 4
> Incorrect syntax near 'IX_OmimVarLocusIdSNP'.
> what is wrong with it?

The syntax is:
WITH (INDEX (IX_OmimVarLocusIdSNP) )

Why on earth are you using Full Outer Joins??????? Do you even
understand what they are for? Change them back to Inner Joins!

9 May 2006 4:15 AM

Fairy239

my teacher want me to get output everything out with nulls so i have i to to
full join

btw the forcepaln fail it take twice as long to excute the files.

Show quote

"krisk***@hotmail.com" wrote:

> Fairy239 wrote:
> > i got the indx out but now the problem how to execute the force plan
> > i did this
> > name of my index(IX_OmimVarLocusIdSNP)
> > Set forceplan on
> > SELECT Distinct alt_ti.Omim_no,OmimVarLocusIdSNP.snp_id,Gene.Gene_Name
> > From alt_ti
> > FULL OUTER JOIN OmimVarLocusIdSNP WITH (INDEX IX_OmimVarLocusIdSNP)
> > ON alt_ti.Omim_No=OmimVarLocusIdSNP.omim_id
> > FULL OUTER JOIN Gene
> > ON alt_ti.Omim_No=Gene.Omim_No
> > Where
> > alt_ti.Omim_Alt_Titles Like '%LIVER%'
> > ORDER BY Omim_No ASC
> > Set forceplan off
> >
> > i got this error
> > Msg 102, Level 15, State 1, Line 4
> > Incorrect syntax near 'IX_OmimVarLocusIdSNP'.
> > what is wrong with it?
>
> The syntax is:
> WITH (INDEX (IX_OmimVarLocusIdSNP) )
>
> Why on earth are you using Full Outer Joins??????? Do you even
> understand what they are for? Change them back to Inner Joins!
>
>

9 May 2006 12:53 PM

Jim Underwood

You should not be using force plan except as a last resort. With proper
indexes, joins, and statistics, SQL Server will usually choose the best
execution plan for you. Right now you do not seem to have any of these
things, and as Kriskirk said, you need a class (or book) on relational
database management before you will be able to do anything.

This is a very good place to get specific answers to specific questions, but
only after you have a good understanding of the basics. Unfortunately, you
don't have enough basic knowledge at this point, and you need to learn a lot
before you will be able to give us the necessary information to help you.

Trying to explain to someone who is new to database concepts how to tune a
query that is going against 100 million rows of data in 6 to 12 tables is
like trying to explain to tell a programmer how to perform triple bypass
surgery on a heart attack victem. With advice from the best doctor in the
world, you would end up killing the patient. With advice from everyone
here, you may be able to ruin your system.

Take a class, read a book, or hire a consultant. I applaud your willingness
to try to work with this database, but you REALLY need to educate yourself
more before you will be able to do anything other than cause damage.

Show quote

"Fairy239" <Fairy***@discussions.microsoft.com> wrote in message
news:DD2FC0F0-6A51-469B-8525-3847135A1A42@microsoft.com...
> my teacher want me to get output everything out with nulls so i have i to
to
> full join
>
> btw the forcepaln fail it take twice as long to excute the files.
>
>
> "krisk***@hotmail.com" wrote:
>
> > Fairy239 wrote:
> > > i got the indx out but now the problem how to execute the force plan
> > > i did this
> > > name of my index(IX_OmimVarLocusIdSNP)
> > > Set forceplan on
> > > SELECT Distinct alt_ti.Omim_no,OmimVarLocusIdSNP.snp_id,Gene.Gene_Name
> > > From alt_ti
> > > FULL OUTER JOIN OmimVarLocusIdSNP WITH (INDEX IX_OmimVarLocusIdSNP)
> > > ON alt_ti.Omim_No=OmimVarLocusIdSNP.omim_id
> > > FULL OUTER JOIN Gene
> > > ON alt_ti.Omim_No=Gene.Omim_No
> > > Where
> > > alt_ti.Omim_Alt_Titles Like '%LIVER%'
> > > ORDER BY Omim_No ASC
> > > Set forceplan off
> > >
> > > i got this error
> > > Msg 102, Level 15, State 1, Line 4
> > > Incorrect syntax near 'IX_OmimVarLocusIdSNP'.
> > > what is wrong with it?
> >
> > The syntax is:
> > WITH (INDEX (IX_OmimVarLocusIdSNP) )
> >
> > Why on earth are you using Full Outer Joins??????? Do you even
> > understand what they are for? Change them back to Inner Joins!
> >
> >