Row performance

Hi.
I have a simple question.
What is the maximum number of rows in a table before MS SQL takes more
time to execute a query or the performance will slow down ' Is it
possible that the number is 1 000 000 ? If I remember, I saw that on
the web but I'm not sure. Consider that my table have been indexed.
Am I right or wrong '
Thanks.
Jonathan Chretien
Analyst/ProgrammerI would also expect for a non-indexed table, that performance would scale
linearly corresponding directly to the number of records in the table.
--
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 community
of SQL Server professionals.
www.sqlpass.org
"Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
news:3F27BEA0.41743AB0@.amisco.moc...
> Hi.
> I have a simple question.
> What is the maximum number of rows in a table before MS SQL takes more
> time to execute a query or the performance will slow down ' Is it
> possible that the number is 1 000 000 ? If I remember, I saw that on
> the web but I'm not sure. Consider that my table have been indexed.
> Am I right or wrong '
>
> Thanks.
>
> Jonathan Chretien
> Analyst/Programmer
>|||Hi.
My table is correctly indexed. I have only one index and my query is base on the
same field of my index. This table come from an AS400 and when I execute this
query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
then my MS SQL Server. I execute the query when the server is not load,
approximatly an average of 2 to 5% and the maximum peak that I receive from my
SQL Server is 50%.
Specification of my MS SQL Server:
Version: MS SQL Server 7.0
CPU: 2 PII Xeon 550
Memory: 512 meg
Disk: 40 gig
Database size 1.6 gig
Avg CPU utilization: Between 10 to 20 %
Jonathan Chretien
Analyst/Programmer
Wayne Snyder a écrit :
> I would also expect for a non-indexed table, that performance would scale
> linearly corresponding directly to the number of records in the table.
> --
> 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 community
> of SQL Server professionals.
> www.sqlpass.org
> "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> news:3F27BEA0.41743AB0@.amisco.moc...
> > Hi.
> >
> > I have a simple question.
> >
> > What is the maximum number of rows in a table before MS SQL takes more
> > time to execute a query or the performance will slow down ' Is it
> > possible that the number is 1 000 000 ? If I remember, I saw that on
> > the web but I'm not sure. Consider that my table have been indexed.
> >
> > Am I right or wrong '
> >
> >
> > Thanks.
> >
> >
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >|||Yes.
Query plan is using my index and have a cost of 81%.
Jonathan Chretien
Analyst/Programmer
Gert-Jan Strik a écrit :
> Check the execution plan to see if SQL-Server is actually using the
> index. There might be some reason why the index is disqualified. For
> example when your statistics are out of date, or when your predicate is
> not SARGuable.
> Hope this helps,
> Gert-Jan
> Jonathan Chretien wrote:
> >
> > Hi.
> >
> > My table is correctly indexed. I have only one index and my query is base on the
> > same field of my index. This table come from an AS400 and when I execute this
> > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > then my MS SQL Server. I execute the query when the server is not load,
> > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > SQL Server is 50%.
> >
> > Specification of my MS SQL Server:
> > Version: MS SQL Server 7.0
> > CPU: 2 PII Xeon 550
> > Memory: 512 meg
> > Disk: 40 gig
> > Database size 1.6 gig
> > Avg CPU utilization: Between 10 to 20 %
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >
> > Wayne Snyder a écrit :
> >
> > > I would also expect for a non-indexed table, that performance would scale
> > > linearly corresponding directly to the number of records in the table.
> > >
> > > --
> > > 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 community
> > > of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > Hi.
> > > >
> > > > I have a simple question.
> > > >
> > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > time to execute a query or the performance will slow down ' Is it
> > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > the web but I'm not sure. Consider that my table have been indexed.
> > > >
> > > > Am I right or wrong '
> > > >
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > > > Jonathan Chretien
> > > > Analyst/Programmer
> > > >|||Jonathan,
1. Is it performing an index SEEK (good) or index SCAN (bad)?
2. Is it a clustered or non-clustered index?
Gert-Jan
Jonathan Chretien wrote:
> Yes.
> Query plan is using my index and have a cost of 81%.
> Jonathan Chretien
> Analyst/Programmer
> Gert-Jan Strik a écrit :
> > Check the execution plan to see if SQL-Server is actually using the
> > index. There might be some reason why the index is disqualified. For
> > example when your statistics are out of date, or when your predicate is
> > not SARGuable.
> >
> > Hope this helps,
> > Gert-Jan
> >
> > Jonathan Chretien wrote:
> > >
> > > Hi.
> > >
> > > My table is correctly indexed. I have only one index and my query is base on the
> > > same field of my index. This table come from an AS400 and when I execute this
> > > query on the AS400, it takes 0 to 1 secondes. When I execute the same query on
> > > my MS SQL Server, it takes 4 to 5 secondes to be execute. The AS400 is more load
> > > then my MS SQL Server. I execute the query when the server is not load,
> > > approximatly an average of 2 to 5% and the maximum peak that I receive from my
> > > SQL Server is 50%.
> > >
> > > Specification of my MS SQL Server:
> > > Version: MS SQL Server 7.0
> > > CPU: 2 PII Xeon 550
> > > Memory: 512 meg
> > > Disk: 40 gig
> > > Database size 1.6 gig
> > > Avg CPU utilization: Between 10 to 20 %
> > >
> > > Jonathan Chretien
> > > Analyst/Programmer
> > >
> > > Wayne Snyder a écrit :
> > >
> > > > I would also expect for a non-indexed table, that performance would scale
> > > > linearly corresponding directly to the number of records in the table.
> > > >
> > > > --
> > > > 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 community
> > > > of SQL Server professionals.
> > > > www.sqlpass.org
> > > >
> > > > "Jonathan Chretien" <jonathan.chretien@.amisco.moc> wrote in message
> > > > news:3F27BEA0.41743AB0@.amisco.moc...
> > > > > Hi.
> > > > >
> > > > > I have a simple question.
> > > > >
> > > > > What is the maximum number of rows in a table before MS SQL takes more
> > > > > time to execute a query or the performance will slow down ' Is it
> > > > > possible that the number is 1 000 000 ? If I remember, I saw that on
> > > > > the web but I'm not sure. Consider that my table have been indexed.
> > > > >
> > > > > Am I right or wrong '
> > > > >
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > >
> > > > > Jonathan Chretien
> > > > > Analyst/Programmer
> > > > >