Problem: sql server 2000 server low performance. thanks:)

I have a table of 60,000rows. There are 47 fields in the table. 43 of them
are nvarchar with length of 100. Two of them are big nvarchar with length of
1024 and 2048. an indexno field is int and the other is date. two primary
keys, one of them is indexno;the other is nvarchar.
Low performance:
1.using query of 'like', it shows timeout. I have to use indexno to control
the number of the rows i am querying.
2.When I use application program to query some length of bytes, it costs
quite a lot of minutes.
Any one can help?
Thanks in advance.treese
Do you have appropriate indexes for your table?
Using LIKE '%Something' may prevent from query optimizer to use an index.
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Thanks! Uri Dimant
I have tried to set up index on those frequently used fiels. It doesn't
work. Any other clues?
treesy
"Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢ÐÂÎÅ
:eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> treese
> Do you have appropriate indexes for your table?
> Using LIKE '%Something' may prevent from query optimizer to use an index.
>
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>|||Did you see that after adding these indexes the optimizer was able to use
them?
"treesy" <treesy@.hostran.com.cn> wrote in message
news:utAb5uT9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Thanks! Uri Dimant
> I have tried to set up index on those frequently used fiels. It doesn't
> work. Any other clues?
> treesy
> "Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢ÐÂÎÅ
> :eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> > treese
> > Do you have appropriate indexes for your table?
> > Using LIKE '%Something' may prevent from query optimizer to use an
index.
> >
> >
> >
> >
> > "treesy" <treesy@.hostran.com.cn> wrote in message
> > news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > > I have a table of 60,000rows. There are 47 fields in the table. 43 of
> them
> > > are nvarchar with length of 100. Two of them are big nvarchar with
> length
> > of
> > > 1024 and 2048. an indexno field is int and the other is date. two
> primary
> > > keys, one of them is indexno;the other is nvarchar.
> > >
> > > Low performance:
> > > 1.using query of 'like', it shows timeout. I have to use indexno to
> > control
> > > the number of the rows i am querying.
> > > 2.When I use application program to query some length of bytes, it
costs
> > > quite a lot of minutes.
> > > Any one can help?
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>|||If your like clause begins with a wildcard, it is unlikely that SQL will use
the index(in an index seek), however it may do an index scan of the leaf
level...
Look at the graphical showplan to see which access method the optimizer is
choosing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Hi, thank you all!
I have solved the problem! I split my big table into 5 tables. The
performance is good this time.
"Wayne Snyder" <wsnyder@.computeredservices.com> дÈëÏûÏ¢ÐÂÎÅ
:ufDflpV9DHA.972@.tk2msftngp13.phx.gbl...
> If your like clause begins with a wildcard, it is unlikely that SQL will
use
> the index(in an index seek), however it may do an index scan of the leaf
> level...
> Look at the graphical showplan to see which access method the optimizer is
> choosing.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>