SQL 2000 Perfomance

SQL 2000 Perfomance

Post by An » Fri, 26 Apr 2002 01:47:04



Hi,

What would you think of the hardware requirement for SQL2000 to
support a
30 Gb Database , with 1 Million to 5 million records in the main
tables(5).

The actual server is IBM 6000R ( 2 processors 700, 1Gb Ram), to run a
query against two tables with over one million records each is taking
6 minutes.

Your comments are greatly appreciated. I have no experience in
handling such
a huge database. :(

 
 
 

SQL 2000 Perfomance

Post by Andrew J. Kell » Fri, 26 Apr 2002 02:51:42


It doesn't sound like this is a hardware issue but more of having the right
indexes and queries.  Can you post the DDL for the tables involved and the
query?

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:> Hi,

> What would you think of the hardware requirement for SQL2000 to
> support a
> 30 Gb Database , with 1 Million to 5 million records in the main
> tables(5).

> The actual server is IBM 6000R ( 2 processors 700, 1Gb Ram), to run a
> query against two tables with over one million records each is taking
> 6 minutes.

> Your comments are greatly appreciated. I have no experience in
> handling such
> a huge database. :(


 
 
 

SQL 2000 Perfomance

Post by An » Fri, 26 Apr 2002 07:40:21


Hi Andrew,

I can post the scripts for tables, but the id's for each table are
binary fields generated by a store procedure. The application is
Pivotal eRelationship.
Both tables are indexed on the id's, and the contact table is also
index on the last_name, the query is :
"Select * from contact,order_ where contact.last_name like 'A%' and
contact.contact_id = Order_.bill_to_contact_id"
I tried the query from the query analizer, not from the application.
There are no links between tables at database level.

Thank you,
Ana


> It doesn't sound like this is a hardware issue but more of having the right
> indexes and queries.  Can you post the DDL for the tables involved and the
> query?

> --
> Andrew J. Kelly   SQL MVP
> Targitinteractive, Inc.



> > Hi,

> > What would you think of the hardware requirement for SQL2000 to
> > support a
> > 30 Gb Database , with 1 Million to 5 million records in the main
> > tables(5).

> > The actual server is IBM 6000R ( 2 processors 700, 1Gb Ram), to run a
> > query against two tables with over one million records each is taking
> > 6 minutes.

> > Your comments are greatly appreciated. I have no experience in
> > handling such
> > a huge database. :(

 
 
 

SQL 2000 Perfomance

Post by Andrew J. Kell » Fri, 26 Apr 2002 09:00:12


Ana,

I have never seen anyone use a binary datatype for the Primary and foreign
Keys. How large are they?  Do you have a Clustered index on these?  How many
rows will this query return?  It seems like it will be a lot.  A few
thoughts on the query.  Is this the actual one?  If so you may want to
choose only the actual columns you truly require and not use *.   Do you
really need ALL the rows for everyone with a last name that starts with 'A'?
Usually that much data is too much for a human to process efficiently.  If
you have millions of rows in the table I would expect 10's of thousands of
matching rows (or more).  If this is really true then sql is probably
choosing a table scan which would be more efficient than using the index
unless it was a clustered index.  Query analyzer will show that one way or
the other if you turn on the showplan.

--
Andrew J. Kelly,  SQL Server MVP
TargitInteractive


> Hi Andrew,

> I can post the scripts for tables, but the id's for each table are
> binary fields generated by a store procedure. The application is
> Pivotal eRelationship.
> Both tables are indexed on the id's, and the contact table is also
> index on the last_name, the query is :
> "Select * from contact,order_ where contact.last_name like 'A%' and
> contact.contact_id = Order_.bill_to_contact_id"
> I tried the query from the query analizer, not from the application.
> There are no links between tables at database level.

> Thank you,
> Ana




- Show quoted text -

> > It doesn't sound like this is a hardware issue but more of having the
right
> > indexes and queries.  Can you post the DDL for the tables involved and
the
> > query?

> > --
> > Andrew J. Kelly   SQL MVP
> > Targitinteractive, Inc.



> > > Hi,

> > > What would you think of the hardware requirement for SQL2000 to
> > > support a
> > > 30 Gb Database , with 1 Million to 5 million records in the main
> > > tables(5).

> > > The actual server is IBM 6000R ( 2 processors 700, 1Gb Ram), to run a
> > > query against two tables with over one million records each is taking
> > > 6 minutes.

> > > Your comments are greatly appreciated. I have no experience in
> > > handling such
> > > a huge database. :(

 
 
 

SQL 2000 Perfomance

Post by Ana Mois » Fri, 26 Apr 2002 10:51:21


Hi Andrew,

This is first time when I saw binary datatype for primary key, the id
field looks like "00000000000006D3". The indexes for id's are clustered.
That query was just a test query, the real one is linking 5 tables(with
over 1 million records and some of them more then 70 fields), and it
takes more than 6 minutes to do the select and insert into a temporary
table.I am using insert into table from select.I can send the stored
procedure by email to you, maybe you can give me some suggestion
regarding what to change. Sorry, is the first time when I have to deal
with a database of this size, so I'm kind of lost.

Thank you,
Ana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

  processinfo.txt
2K Download
 
 
 

SQL 2000 Perfomance

Post by Dean Thompso » Fri, 26 Apr 2002 13:31:45


Hi Ana,

If you are running a query against 5 million records on each table, and let's assume for a moment that there are 5m/26
records where name like 'A%' (since A is one of 26 letters), then you are looking at approximately 192,000 records.
Since you are using the * in your select list, you are getting data from both tables.  Judging by your primary key
alone, the data must be pretty wide.  Let's assume 1KB per record, then we would be looking at SQL Server needing to
supply you with 192,000,000 bytes.  Not a small amount by any means.  

Among other things, a large amount of the time is probably being spent by the client, Query Analyzer, setting aside the
space in the results window.  Beyond that, there are other concerns, such as how much IO this requires, and how ordered
the records are.  If the records could be assumed to be all on contiguous pages, then you would be looking approximately
24,000 pages of IO.  However, since the clustered index is on the ID, you are probably doing a clustered index scan
(i.e. table scan when a clustered index is present) with a filter at the end, generating a significant amount of CPU
usage and iterating through the entire contact table.  What's your Cache Hit Ratio when this query is running?

Try your query with SET SHOWPLAN_ALL ON (or press Ctrl - L for a graphic version).  This will help you see the work that
SQL Server is trying to do.  

You can fix some of this by
1) Retrieving only the fields you need.
2) Take a look at network capacity, the more the merrier
3) Restrict the query more (i.e. fewer records)
4) Check out your disk hardware (how high is the Disk Queue getting?)

All these things will add up.  If you want to throw hardware at it, tell us more about your disk configuration.  Also,
where is your TempDB?  Is it on its own volume, or sharing one with the primary database files?  More CPUs won't help
much here.

HTH,
Dean


>Hi Andrew,

>I can post the scripts for tables, but the id's for each table are
>binary fields generated by a store procedure. The application is
>Pivotal eRelationship.
>Both tables are indexed on the id's, and the contact table is also
>index on the last_name, the query is :
>"Select * from contact,order_ where contact.last_name like 'A%' and
>contact.contact_id = Order_.bill_to_contact_id"
>I tried the query from the query analizer, not from the application.
>There are no links between tables at database level.

>Thank you,
>Ana


>> It doesn't sound like this is a hardware issue but more of having the right
>> indexes and queries.  Can you post the DDL for the tables involved and the
>> query?

>> --
>> Andrew J. Kelly   SQL MVP
>> Targitinteractive, Inc.



>> > Hi,

>> > What would you think of the hardware requirement for SQL2000 to
>> > support a
>> > 30 Gb Database , with 1 Million to 5 million records in the main
>> > tables(5).

>> > The actual server is IBM 6000R ( 2 processors 700, 1Gb Ram), to run a
>> > query against two tables with over one million records each is taking
>> > 6 minutes.

>> > Your comments are greatly appreciated. I have no experience in
>> > handling such
>> > a huge database. :(

Dean Thompson <deanATtxsqlusers.com>
Texas SQL Users <http://www.txsqlusers.com>

Add your SQL Server or Developer website to our
Web Resources directory at
http://www.txsqlusers.com/txLinks/add.asp

Sign up for our free monthly newsletter at
http://lb.bcentral.com/ex/manage/subscriberprefs.aspx?customerid=9828

 
 
 

SQL 2000 Perfomance

Post by Andrew J. Kell » Fri, 26 Apr 2002 21:32:44


Ana,

It would be pretty hard pressed to get a query of that magnitude down less
than 6 minutes without some real beefy hardware.   Joining 5 tables with 70
columns and returning over a million rows is a pretty hefty query especially
if you have ID columns of that size.   What is this query used for?  Is this
something run on a regular basis or is this a special case?  As stated
before there are very rare cases when you actually need (or can actually
effectively use) that much data in one result set.  Since I don't know your
application I can't dismiss that fact that it may be viable but I would
strongly advise seeing if you can cut down the number of rows returned and
also the number of columns.  As Dean pointed out that is just one massive
result set.   Any chance you can change some of the datatypes?  Maybe change
the ID's to an INTEGER.  It could be  allot of work but might cut down the
size of your DB quite a bit.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:> Hi Andrew,

> This is first time when I saw binary datatype for primary key, the id
> field looks like "00000000000006D3". The indexes for id's are clustered.
> That query was just a test query, the real one is linking 5 tables(with
> over 1 million records and some of them more then 70 fields), and it
> takes more than 6 minutes to do the select and insert into a temporary
> table.I am using insert into table from select.I can send the stored
> procedure by email to you, maybe you can give me some suggestion
> regarding what to change. Sorry, is the first time when I have to deal
> with a database of this size, so I'm kind of lost.

> Thank you,
> Ana

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

SQL 2000 Perfomance

Post by Ana Mois » Sat, 27 Apr 2002 03:35:56


Hi Andrew and Dean,

The result set is not that big, because I'm selecting only the fields
that I need, and there are no more then 1000-2000 records.
Changing the ID's to an INTEGER is not an option.
The SQL Server has 2 array controllers,
1st array with 2 SCSI HDD, in mirror, for the operating system
2nd array with 4 SCSI HDD , a partition of total 101 Gb for the
databases. the free space right now is 20 gb.
The tempdb is on the same partition with the database.
I don't have access to that server right now to get more query
statistics

Do you think that adding more memory will help. Like 8Gb or 16Gb.

The query select around 60 fields. I will post part of the query:

insert into TempTableNuco
  select  serial_no = left(m.medicalert_id,len m.medicalert_id)-1)  ,
   name_ = Case When m.Middle_Name IS NULL Then m.First_Name + space(1)
+ m.Last_Name Else m.First_Name + space(1)+ m.Middle_Name +space(1)+
m.Last_Name End,
   language_ = Case When (m.Preferred_Language ='English' or
m.Preferred_Language ='French') Then
Upper(Substring(m.Preferred_Language,1,1)) Else '' End,
    sex = Case When m.Gender = 1 Then 'M' Else Case When    m.Gender = 0
Then 'F' Else '' End End ,
    sin_= isnull(m.Sin,''),
    change_date = Case when (m.rn_edit_date is null and p.Rn_Edit_date
is null)  then   replace(CONVERT ( varchar (11)
,isnull(m.Enrolment_date,getdate()),106 ),' ','-')   else Case When
datediff(dd,m.Rn_Edit_Date,p.Rn_Edit_Date)>0 Then  replace(CONVERT (
varchar (11) , m.Rn_Edit_date,106 ),' ','-') Else replace(CONVERT (
varchar (11) , p.Rn_Edit_date,106 ),' ','-') End  End ,
    group_code=orddet.order_detail_id,
    member_home_phone = Case When (m.Phone is null or m.Phone ='+1 (111)
1111111') Then '' Else SUBSTRING(m.Phone, 5, 3) + SUBSTRING(m.Phone, 10,
3) + SUBSTRING(m.Phone, 14, 12) End ,
    business_ext = IsNull( m.Extension,''),
    year_of_birth = Case When Year(m.Birthday)='1800' Then '' Else Case
When m.Birthday_Approx=1 Then convert(char(4),datepart(year,m.Birthday))
Else datepart(year,m.Birthday)+datepart(month,m.Birthday)+
datepart(day,m.Birthday) End End ,.................

from    order_detail orddet, order_ o, contact m,
     medical_profile p, product prod, payment_detail pay, [system] ,
company comp

where  
        Upper(o.Order_Type)<>'REPAIR'
        and o.Order__Id = orddet.Order__Id  
        and o.Balance_Owing < system.Min_Balance_Owing  
         and  (Upper(o.Status) = 'APPROVED'  or Upper(o.Status) = 'IN PROCESS
ENGRAVER')
        and orddet.Bundle_Number is null
        and orddet.Product_id = prod.Product_id  
        and prod.Type='ID Device'
        and prod.manufacturer = comp.company_id
        and  comp.Company_name = 'XXXX'
        and  o.Bill_To_Contact_Id = m.Contact_Id  
        and m.medicalert_id is not null
        and m.Contact_Id = p.member_id  
        and p.Active = 1
        and p.Rn_create_date = (select
               max(Rn_Create_date)  
               from medical_profile  
               where m.Contact_Id = member_id)
         and orddet.Order__Id *= pay.Order_Id
         and pay.Payment_Type like 'Error%'
order by emblem_type,metal_type, case when emblem_type='S' then
chain_type  else cast(length as varchar) end

Thank you,
Ana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SQL 2000 Perfomance

Post by Ana Mois » Sat, 27 Apr 2002 03:35:54


Hi Andrew and Dean,

The result set is not that big, because I'm selecting only the fields
that I need, and there are no more then 1000-2000 records.
Changing the ID's to an INTEGER is not an option.
The SQL Server has 2 array controllers,
1st array with 2 SCSI HDD, in mirror, for the operating system
2nd array with 4 SCSI HDD , a partition of total 101 Gb for the
databases. the free space right now is 20 gb.
The tempdb is on the same partition with the database.
I don't have access to that server right now to get more query
statistics

Do you think that adding more memory will help. Like 8Gb or 16Gb.

The query select around 60 fields. I will post part of the query:

insert into TempTableNuco
  select  serial_no = left(m.medicalert_id,len m.medicalert_id)-1)  ,
   name_ = Case When m.Middle_Name IS NULL Then m.First_Name + space(1)
+ m.Last_Name Else m.First_Name + space(1)+ m.Middle_Name +space(1)+
m.Last_Name End,
   language_ = Case When (m.Preferred_Language ='English' or
m.Preferred_Language ='French') Then
Upper(Substring(m.Preferred_Language,1,1)) Else '' End,
    sex = Case When m.Gender = 1 Then 'M' Else Case When    m.Gender = 0
Then 'F' Else '' End End ,
    sin_= isnull(m.Sin,''),
    change_date = Case when (m.rn_edit_date is null and p.Rn_Edit_date
is null)  then   replace(CONVERT ( varchar (11)
,isnull(m.Enrolment_date,getdate()),106 ),' ','-')   else Case When
datediff(dd,m.Rn_Edit_Date,p.Rn_Edit_Date)>0 Then  replace(CONVERT (
varchar (11) , m.Rn_Edit_date,106 ),' ','-') Else replace(CONVERT (
varchar (11) , p.Rn_Edit_date,106 ),' ','-') End  End ,
    group_code=orddet.order_detail_id,
    member_home_phone = Case When (m.Phone is null or m.Phone ='+1 (111)
1111111') Then '' Else SUBSTRING(m.Phone, 5, 3) + SUBSTRING(m.Phone, 10,
3) + SUBSTRING(m.Phone, 14, 12) End ,
    business_ext = IsNull( m.Extension,''),
    year_of_birth = Case When Year(m.Birthday)='1800' Then '' Else Case
When m.Birthday_Approx=1 Then convert(char(4),datepart(year,m.Birthday))
Else datepart(year,m.Birthday)+datepart(month,m.Birthday)+
datepart(day,m.Birthday) End End ,.................

from    order_detail orddet, order_ o, contact m,
     medical_profile p, product prod, payment_detail pay, [system] ,
company comp

where  
        Upper(o.Order_Type)<>'REPAIR'
        and o.Order__Id = orddet.Order__Id  
        and o.Balance_Owing < system.Min_Balance_Owing  
         and  (Upper(o.Status) = 'APPROVED'  or Upper(o.Status) = 'IN PROCESS
ENGRAVER')
        and orddet.Bundle_Number is null
        and orddet.Product_id = prod.Product_id  
        and prod.Type='ID Device'
        and prod.manufacturer = comp.company_id
        and  comp.Company_name = 'XXXX'
        and  o.Bill_To_Contact_Id = m.Contact_Id  
        and m.medicalert_id is not null
        and m.Contact_Id = p.member_id  
        and p.Active = 1
        and p.Rn_create_date = (select
               max(Rn_Create_date)  
               from medical_profile  
               where m.Contact_Id = member_id)
         and orddet.Order__Id *= pay.Order_Id
         and pay.Payment_Type like 'Error%'
order by emblem_type,metal_type, case when emblem_type='S' then
chain_type  else cast(length as varchar) end

Thank you,
Ana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SQL 2000 Perfomance

Post by Ana Mois » Sat, 27 Apr 2002 03:35:56


Hi Andrew and Dean,

The result set is not that big, because I'm selecting only the fields
that I need, and there are no more then 1000-2000 records.
Changing the ID's to an INTEGER is not an option.
The SQL Server has 2 array controllers,
1st array with 2 SCSI HDD, in mirror, for the operating system
2nd array with 4 SCSI HDD , a partition of total 101 Gb for the
databases. the free space right now is 20 gb.
The tempdb is on the same partition with the database.
I don't have access to that server right now to get more query
statistics

Do you think that adding more memory will help. Like 8Gb or 16Gb.

The query select around 60 fields. I will post part of the query:

insert into TempTableNuco
  select  serial_no = left(m.medicalert_id,len m.medicalert_id)-1)  ,
   name_ = Case When m.Middle_Name IS NULL Then m.First_Name + space(1)
+ m.Last_Name Else m.First_Name + space(1)+ m.Middle_Name +space(1)+
m.Last_Name End,
   language_ = Case When (m.Preferred_Language ='English' or
m.Preferred_Language ='French') Then
Upper(Substring(m.Preferred_Language,1,1)) Else '' End,
    sex = Case When m.Gender = 1 Then 'M' Else Case When    m.Gender = 0
Then 'F' Else '' End End ,
    sin_= isnull(m.Sin,''),
    change_date = Case when (m.rn_edit_date is null and p.Rn_Edit_date
is null)  then   replace(CONVERT ( varchar (11)
,isnull(m.Enrolment_date,getdate()),106 ),' ','-')   else Case When
datediff(dd,m.Rn_Edit_Date,p.Rn_Edit_Date)>0 Then  replace(CONVERT (
varchar (11) , m.Rn_Edit_date,106 ),' ','-') Else replace(CONVERT (
varchar (11) , p.Rn_Edit_date,106 ),' ','-') End  End ,
    group_code=orddet.order_detail_id,
    member_home_phone = Case When (m.Phone is null or m.Phone ='+1 (111)
1111111') Then '' Else SUBSTRING(m.Phone, 5, 3) + SUBSTRING(m.Phone, 10,
3) + SUBSTRING(m.Phone, 14, 12) End ,
    business_ext = IsNull( m.Extension,''),
    year_of_birth = Case When Year(m.Birthday)='1800' Then '' Else Case
When m.Birthday_Approx=1 Then convert(char(4),datepart(year,m.Birthday))
Else datepart(year,m.Birthday)+datepart(month,m.Birthday)+
datepart(day,m.Birthday) End End ,.................

from    order_detail orddet, order_ o, contact m,
     medical_profile p, product prod, payment_detail pay, [system] ,
company comp

where  
        Upper(o.Order_Type)<>'REPAIR'
        and o.Order__Id = orddet.Order__Id  
        and o.Balance_Owing < system.Min_Balance_Owing  
         and  (Upper(o.Status) = 'APPROVED'  or Upper(o.Status) = 'IN PROCESS
ENGRAVER')
        and orddet.Bundle_Number is null
        and orddet.Product_id = prod.Product_id  
        and prod.Type='ID Device'
        and prod.manufacturer = comp.company_id
        and  comp.Company_name = 'XXXX'
        and  o.Bill_To_Contact_Id = m.Contact_Id  
        and m.medicalert_id is not null
        and m.Contact_Id = p.member_id  
        and p.Active = 1
        and p.Rn_create_date = (select
               max(Rn_Create_date)  
               from medical_profile  
               where m.Contact_Id = member_id)
         and orddet.Order__Id *= pay.Order_Id
         and pay.Payment_Type like 'Error%'
order by emblem_type,metal_type, case when emblem_type='S' then
chain_type  else cast(length as varchar) end

Thank you,
Ana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SQL 2000 Perfomance

Post by Ana Mois » Sat, 27 Apr 2002 03:35:55


Hi Andrew and Dean,

The result set is not that big, because I'm selecting only the fields
that I need, and there are no more then 1000-2000 records.
Changing the ID's to an INTEGER is not an option.
The SQL Server has 2 array controllers,
1st array with 2 SCSI HDD, in mirror, for the operating system
2nd array with 4 SCSI HDD , a partition of total 101 Gb for the
databases. the free space right now is 20 gb.
The tempdb is on the same partition with the database.
I don't have access to that server right now to get more query
statistics

Do you think that adding more memory will help. Like 8Gb or 16Gb.

The query select around 60 fields. I will post part of the query:

insert into TempTableNuco
  select  serial_no = left(m.medicalert_id,len m.medicalert_id)-1)  ,
   name_ = Case When m.Middle_Name IS NULL Then m.First_Name + space(1)
+ m.Last_Name Else m.First_Name + space(1)+ m.Middle_Name +space(1)+
m.Last_Name End,
   language_ = Case When (m.Preferred_Language ='English' or
m.Preferred_Language ='French') Then
Upper(Substring(m.Preferred_Language,1,1)) Else '' End,
    sex = Case When m.Gender = 1 Then 'M' Else Case When    m.Gender = 0
Then 'F' Else '' End End ,
    sin_= isnull(m.Sin,''),
    change_date = Case when (m.rn_edit_date is null and p.Rn_Edit_date
is null)  then   replace(CONVERT ( varchar (11)
,isnull(m.Enrolment_date,getdate()),106 ),' ','-')   else Case When
datediff(dd,m.Rn_Edit_Date,p.Rn_Edit_Date)>0 Then  replace(CONVERT (
varchar (11) , m.Rn_Edit_date,106 ),' ','-') Else replace(CONVERT (
varchar (11) , p.Rn_Edit_date,106 ),' ','-') End  End ,
    group_code=orddet.order_detail_id,
    member_home_phone = Case When (m.Phone is null or m.Phone ='+1 (111)
1111111') Then '' Else SUBSTRING(m.Phone, 5, 3) + SUBSTRING(m.Phone, 10,
3) + SUBSTRING(m.Phone, 14, 12) End ,
    business_ext = IsNull( m.Extension,''),
    year_of_birth = Case When Year(m.Birthday)='1800' Then '' Else Case
When m.Birthday_Approx=1 Then convert(char(4),datepart(year,m.Birthday))
Else datepart(year,m.Birthday)+datepart(month,m.Birthday)+
datepart(day,m.Birthday) End End ,.................

from    order_detail orddet, order_ o, contact m,
     medical_profile p, product prod, payment_detail pay, [system] ,
company comp

where  
        Upper(o.Order_Type)<>'REPAIR'
        and o.Order__Id = orddet.Order__Id  
        and o.Balance_Owing < system.Min_Balance_Owing  
         and  (Upper(o.Status) = 'APPROVED'  or Upper(o.Status) = 'IN PROCESS
ENGRAVER')
        and orddet.Bundle_Number is null
        and orddet.Product_id = prod.Product_id  
        and prod.Type='ID Device'
        and prod.manufacturer = comp.company_id
        and  comp.Company_name = 'XXXX'
        and  o.Bill_To_Contact_Id = m.Contact_Id  
        and m.medicalert_id is not null
        and m.Contact_Id = p.member_id  
        and p.Active = 1
        and p.Rn_create_date = (select
               max(Rn_Create_date)  
               from medical_profile  
               where m.Contact_Id = member_id)
         and orddet.Order__Id *= pay.Order_Id
         and pay.Payment_Type like 'Error%'
order by emblem_type,metal_type, case when emblem_type='S' then
chain_type  else cast(length as varchar) end

Thank you,
Ana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SQL 2000 Perfomance

Post by Ana Mois » Sat, 27 Apr 2002 03:35:56


Hi Andrew and Dean,

The result set is not that big, because I'm selecting only the fields
that I need, and there are no more then 1000-2000 records.
Changing the ID's to an INTEGER is not an option.
The SQL Server has 2 array controllers,
1st array with 2 SCSI HDD, in mirror, for the operating system
2nd array with 4 SCSI HDD , a partition of total 101 Gb for the
databases. the free space right now is 20 gb.
The tempdb is on the same partition with the database.
I don't have access to that server right now to get more query
statistics

Do you think that adding more memory will help. Like 8Gb or 16Gb.

The query select around 60 fields. I will post part of the query:

insert into TempTableNuco
  select  serial_no = left(m.medicalert_id,len m.medicalert_id)-1)  ,
   name_ = Case When m.Middle_Name IS NULL Then m.First_Name + space(1)
+ m.Last_Name Else m.First_Name + space(1)+ m.Middle_Name +space(1)+
m.Last_Name End,
   language_ = Case When (m.Preferred_Language ='English' or
m.Preferred_Language ='French') Then
Upper(Substring(m.Preferred_Language,1,1)) Else '' End,
    sex = Case When m.Gender = 1 Then 'M' Else Case When    m.Gender = 0
Then 'F' Else '' End End ,
    sin_= isnull(m.Sin,''),
    change_date = Case when (m.rn_edit_date is null and p.Rn_Edit_date
is null)  then   replace(CONVERT ( varchar (11)
,isnull(m.Enrolment_date,getdate()),106 ),' ','-')   else Case When
datediff(dd,m.Rn_Edit_Date,p.Rn_Edit_Date)>0 Then  replace(CONVERT (
varchar (11) , m.Rn_Edit_date,106 ),' ','-') Else replace(CONVERT (
varchar (11) , p.Rn_Edit_date,106 ),' ','-') End  End ,
    group_code=orddet.order_detail_id,
    member_home_phone = Case When (m.Phone is null or m.Phone ='+1 (111)
1111111') Then '' Else SUBSTRING(m.Phone, 5, 3) + SUBSTRING(m.Phone, 10,
3) + SUBSTRING(m.Phone, 14, 12) End ,
    business_ext = IsNull( m.Extension,''),
    year_of_birth = Case When Year(m.Birthday)='1800' Then '' Else Case
When m.Birthday_Approx=1 Then convert(char(4),datepart(year,m.Birthday))
Else datepart(year,m.Birthday)+datepart(month,m.Birthday)+
datepart(day,m.Birthday) End End ,.................

from    order_detail orddet, order_ o, contact m,
     medical_profile p, product prod, payment_detail pay, [system] ,
company comp

where  
        Upper(o.Order_Type)<>'REPAIR'
        and o.Order__Id = orddet.Order__Id  
        and o.Balance_Owing < system.Min_Balance_Owing  
         and  (Upper(o.Status) = 'APPROVED'  or Upper(o.Status) = 'IN PROCESS
ENGRAVER')
        and orddet.Bundle_Number is null
        and orddet.Product_id = prod.Product_id  
        and prod.Type='ID Device'
        and prod.manufacturer = comp.company_id
        and  comp.Company_name = 'XXXX'
        and  o.Bill_To_Contact_Id = m.Contact_Id  
        and m.medicalert_id is not null
        and m.Contact_Id = p.member_id  
        and p.Active = 1
        and p.Rn_create_date = (select
               max(Rn_Create_date)  
               from medical_profile  
               where m.Contact_Id = member_id)
         and orddet.Order__Id *= pay.Order_Id
         and pay.Payment_Type like 'Error%'
order by emblem_type,metal_type, case when emblem_type='S' then
chain_type  else cast(length as varchar) end

Thank you,
Ana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SQL 2000 Perfomance

Post by Ana Mois » Sat, 27 Apr 2002 03:59:48


Hi Andrew and Dean,

The result set is not that big, because I'm selecting only the fields
that I need, and there are no more then 1000-2000 records.
Changing the ID's to an INTEGER is not an option.
The SQL Server has 2 array controllers,
1st array with 2 SCSI HDD, in mirror, for the operating system
2nd array with 4 SCSI HDD , a partition of total 101 Gb for the
databases. the free space right now is 20 gb.
The tempdb is on the same partition with the database.
I don't have access to that server right now to get more query
statistics

Do you think that adding more memory will help. Like 8Gb or 16Gb.

The query select around 60 fields. I will post part of the query:

insert into TempTableNuco
  select  serial_no = left(m.medicalert_id,len m.medicalert_id)-1)  ,
   name_ = Case When m.Middle_Name IS NULL Then m.First_Name + space(1)
+ m.Last_Name Else m.First_Name + space(1)+ m.Middle_Name +space(1)+
m.Last_Name End,
   language_ = Case When (m.Preferred_Language ='English' or
m.Preferred_Language ='French') Then
Upper(Substring(m.Preferred_Language,1,1)) Else '' End,
    sex = Case When m.Gender = 1 Then 'M' Else Case When    m.Gender = 0
Then 'F' Else '' End End ,
    sin_= isnull(m.Sin,''),
    change_date = Case when (m.rn_edit_date is null and p.Rn_Edit_date
is null)  then   replace(CONVERT ( varchar (11)
,isnull(m.Enrolment_date,getdate()),106 ),' ','-')   else Case When
datediff(dd,m.Rn_Edit_Date,p.Rn_Edit_Date)>0 Then  replace(CONVERT (
varchar (11) , m.Rn_Edit_date,106 ),' ','-') Else replace(CONVERT (
varchar (11) , p.Rn_Edit_date,106 ),' ','-') End  End ,
    group_code=orddet.order_detail_id,
    member_home_phone = Case When (m.Phone is null or m.Phone ='+1 (111)
1111111') Then '' Else SUBSTRING(m.Phone, 5, 3) + SUBSTRING(m.Phone, 10,
3) + SUBSTRING(m.Phone, 14, 12) End ,
    business_ext = IsNull( m.Extension,''),
    year_of_birth = Case When Year(m.Birthday)='1800' Then '' Else Case
When m.Birthday_Approx=1 Then convert(char(4),datepart(year,m.Birthday))
Else datepart(year,m.Birthday)+datepart(month,m.Birthday)+
datepart(day,m.Birthday) End End ,.................

from    order_detail orddet, order_ o, contact m,
     medical_profile p, product prod, payment_detail pay, [system] ,
company comp

where  
        Upper(o.Order_Type)<>'REPAIR'
        and o.Order__Id = orddet.Order__Id  
        and o.Balance_Owing < system.Min_Balance_Owing  
         and  (Upper(o.Status) = 'APPROVED'  or Upper(o.Status) = 'IN PROCESS
ENGRAVER')
        and orddet.Bundle_Number is null
        and orddet.Product_id = prod.Product_id  
        and prod.Type='ID Device'
        and prod.manufacturer = comp.company_id
        and  comp.Company_name = 'XXXX'
        and  o.Bill_To_Contact_Id = m.Contact_Id  
        and m.medicalert_id is not null
        and m.Contact_Id = p.member_id  
        and p.Active = 1
        and p.Rn_create_date = (select
               max(Rn_Create_date)  
               from medical_profile  
               where m.Contact_Id = member_id)
         and orddet.Order__Id *= pay.Order_Id
         and pay.Payment_Type like 'Error%'
order by emblem_type,metal_type, case when emblem_type='S' then
chain_type  else cast(length as varchar) end

Thank you,
Ana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SQL 2000 Perfomance

Post by Andrew J. Kell » Sat, 27 Apr 2002 04:01:50


Ana,

I don't want you to think that I am just trying to pass you off to some one
else but it seems as if you have several issues with this system. The
hardware and schema sound like they can use some improvements if you are
able to do that.  The query certainly needs some work.  Most of the SARG's
are not able to use an Index even if one did exist. But unfortunately I
think there is too much to be done properly in a newsgroup forum.  You could
benefit most from hiring a consultant who can spend time examining the whole
system to determine the best coarse of action.  With only limited views into
your system there is only so much can be done like this.  Adding ram might
help some since you can't fit all of the DB in cache with only 1GB but again
that really needs more investigation to determine how much it will help.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:

> Hi Andrew and Dean,

> The result set is not that big, because I'm selecting only the fields
> that I need, and there are no more then 1000-2000 records.
> Changing the ID's to an INTEGER is not an option.
> The SQL Server has 2 array controllers,
> 1st array with 2 SCSI HDD, in mirror, for the operating system
> 2nd array with 4 SCSI HDD , a partition of total 101 Gb for the
> databases. the free space right now is 20 gb.
> The tempdb is on the same partition with the database.
> I don't have access to that server right now to get more query
> statistics

> Do you think that adding more memory will help. Like 8Gb or 16Gb.

> The query select around 60 fields. I will post part of the query:

> insert into TempTableNuco
>   select  serial_no = left(m.medicalert_id,len m.medicalert_id)-1)  ,
>    name_ = Case When m.Middle_Name IS NULL Then m.First_Name + space(1)
> + m.Last_Name Else m.First_Name + space(1)+ m.Middle_Name +space(1)+
> m.Last_Name End,
>    language_ = Case When (m.Preferred_Language ='English' or
> m.Preferred_Language ='French') Then
> Upper(Substring(m.Preferred_Language,1,1)) Else '' End,
>     sex = Case When m.Gender = 1 Then 'M' Else Case When    m.Gender = 0
> Then 'F' Else '' End End ,
>     sin_= isnull(m.Sin,''),
>     change_date = Case when (m.rn_edit_date is null and p.Rn_Edit_date
> is null)  then   replace(CONVERT ( varchar (11)
> ,isnull(m.Enrolment_date,getdate()),106 ),' ','-')   else Case When
> datediff(dd,m.Rn_Edit_Date,p.Rn_Edit_Date)>0 Then  replace(CONVERT (
> varchar (11) , m.Rn_Edit_date,106 ),' ','-') Else replace(CONVERT (
> varchar (11) , p.Rn_Edit_date,106 ),' ','-') End  End ,
>     group_code=orddet.order_detail_id,
>     member_home_phone = Case When (m.Phone is null or m.Phone ='+1 (111)
> 1111111') Then '' Else SUBSTRING(m.Phone, 5, 3) + SUBSTRING(m.Phone, 10,
> 3) + SUBSTRING(m.Phone, 14, 12) End ,
>     business_ext = IsNull( m.Extension,''),
>     year_of_birth = Case When Year(m.Birthday)='1800' Then '' Else Case
> When m.Birthday_Approx=1 Then convert(char(4),datepart(year,m.Birthday))
> Else datepart(year,m.Birthday)+datepart(month,m.Birthday)+
> datepart(day,m.Birthday) End End ,.................

> from    order_detail orddet, order_ o, contact m,
>      medical_profile p, product prod, payment_detail pay, [system] ,
> company comp

> where
> Upper(o.Order_Type)<>'REPAIR'
> and o.Order__Id = orddet.Order__Id
> and o.Balance_Owing < system.Min_Balance_Owing
> and  (Upper(o.Status) = 'APPROVED'  or Upper(o.Status) = 'IN PROCESS
> ENGRAVER')
>         and orddet.Bundle_Number is null
>         and orddet.Product_id = prod.Product_id
>         and prod.Type='ID Device'
>         and prod.manufacturer = comp.company_id
>         and  comp.Company_name = 'XXXX'
>         and  o.Bill_To_Contact_Id = m.Contact_Id
>         and m.medicalert_id is not null
>         and m.Contact_Id = p.member_id
>         and p.Active = 1
>         and p.Rn_create_date = (select
>                max(Rn_Create_date)
>                from medical_profile
>                where m.Contact_Id = member_id)
>          and orddet.Order__Id *= pay.Order_Id
>          and pay.Payment_Type like 'Error%'
> order by emblem_type,metal_type, case when emblem_type='S' then
> chain_type  else cast(length as varchar) end

> Thank you,
> Ana

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

SQL 2000 Perfomance

Post by Ana Mois » Sat, 27 Apr 2002 04:12:02


Hi Andrew and Dean,

The result set is not that big, because I'm selecting only the fields
that I need, and there are no more then 1000-2000 records.
Changing the ID's to an INTEGER is not an option.
The SQL Server has 2 array controllers,
1st array with 2 SCSI HDD, in mirror, for the operating system
2nd array with 4 SCSI HDD , a partition of total 101 Gb for the
databases. the free space right now is 20 gb.
The tempdb is on the same partition with the database.
I don't have access to that server right now to get more query
statistics

Do you think that adding more memory will help. Like 8Gb or 16Gb.

The query select around 60 fields. I will post part of the query:

insert into TempTableNuco
  select  serial_no = left(m.medicalert_id,len m.medicalert_id)-1)  ,
   name_ = Case When m.Middle_Name IS NULL Then m.First_Name + space(1)
+ m.Last_Name Else m.First_Name + space(1)+ m.Middle_Name +space(1)+
m.Last_Name End,
   language_ = Case When (m.Preferred_Language ='English' or
m.Preferred_Language ='French') Then
Upper(Substring(m.Preferred_Language,1,1)) Else '' End,
    sex = Case When m.Gender = 1 Then 'M' Else Case When    m.Gender = 0
Then 'F' Else '' End End ,
    sin_= isnull(m.Sin,''),
    change_date = Case when (m.rn_edit_date is null and p.Rn_Edit_date
is null)  then   replace(CONVERT ( varchar (11)
,isnull(m.Enrolment_date,getdate()),106 ),' ','-')   else Case When
datediff(dd,m.Rn_Edit_Date,p.Rn_Edit_Date)>0 Then  replace(CONVERT (
varchar (11) , m.Rn_Edit_date,106 ),' ','-') Else replace(CONVERT (
varchar (11) , p.Rn_Edit_date,106 ),' ','-') End  End ,
    group_code=orddet.order_detail_id,
    member_home_phone = Case When (m.Phone is null or m.Phone ='+1 (111)
1111111') Then '' Else SUBSTRING(m.Phone, 5, 3) + SUBSTRING(m.Phone, 10,
3) + SUBSTRING(m.Phone, 14, 12) End ,
    business_ext = IsNull( m.Extension,''),
    year_of_birth = Case When Year(m.Birthday)='1800' Then '' Else Case
When m.Birthday_Approx=1 Then convert(char(4),datepart(year,m.Birthday))
Else datepart(year,m.Birthday)+datepart(month,m.Birthday)+
datepart(day,m.Birthday) End End ,.................

from    order_detail orddet, order_ o, contact m,
     medical_profile p, product prod, payment_detail pay, [system] ,
company comp

where  
        Upper(o.Order_Type)<>'REPAIR'
        and o.Order__Id = orddet.Order__Id  
        and o.Balance_Owing < system.Min_Balance_Owing  
         and  (Upper(o.Status) = 'APPROVED'  or Upper(o.Status) = 'IN PROCESS
ENGRAVER')
        and orddet.Bundle_Number is null
        and orddet.Product_id = prod.Product_id  
        and prod.Type='ID Device'
        and prod.manufacturer = comp.company_id
        and  comp.Company_name = 'XXXX'
        and  o.Bill_To_Contact_Id = m.Contact_Id  
        and m.medicalert_id is not null
        and m.Contact_Id = p.member_id  
        and p.Active = 1
        and p.Rn_create_date = (select
               max(Rn_Create_date)  
               from medical_profile  
               where m.Contact_Id = member_id)
         and orddet.Order__Id *= pay.Order_Id
         and pay.Payment_Type like 'Error%'
order by emblem_type,metal_type, case when emblem_type='S' then
chain_type  else cast(length as varchar) end

Thank you,
Ana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. sql server 2000 stored procedure very slow perfomance

I am new to sql server 2000 and i have been given the task of bringing
dbase file using clipper code into
sql server 2000 database on a nt4 sp6 machine nice little job.  Well
here is the problem i have written a
stored procedure as follows:

the dates in the where clause are parameters, I hard coded them for
this example.

SELECT tblpolcoverage.AG_NO,
tblpolcoverage.TRANS_DT,tblpolcoverage.EFF_DT, tblpolcoverage.EXP_DT,
tblpolcoverage.POL_NUM,
tblpolcoverage.PREMIUM, tblpolcoverage.TAX, tblpolcoverage.POLFEE,
tblpolcoverage.DRAFTAMT, tblpolcoverage.BALANCE,
tblpolmaster.POL_LNAME, tblpolmaster.POL_FNAME
FROM tblpolcoverage inner join tblpolmaster on
tblpolcoverage.masteridx=tblpolmaster.idx
WHERE tblpolcoverage.TRANS_DT between '04/01/2002' and '04/30/2002'

the two tables tblpolcoverage and tblpolmaster have about 3 million
records in them tblpolmaster.idx field
relates to tblpolcoverage.masteridx field.

returns 209038 rows 48 secs.
This is really slow clipper i would be done a long time ago it is hard
to explain to my boss that we lost time going foward
in software from dbase to sqlserver 2000.
Did i really screw this up or is there any hope.
Please any help.

2. GUIDs in DB2

3. W2K Perfomance Tuning for SQL 2000

4. Master DB Restore

5. Lost Perfomance on SQL 2000 after collation change

6. Is dbload lock the database untill it finish?

7. perfomance degradation after 7.0SP2 to 2000 SP1 upgrade

8. Wanted--Opinions on Microsoft Office 4.2 + Excel 5 for SQL Queries

9. SQL Perfomance Monitor .pmc fails

10. Error on collect of SQL Server perfomance on NT

11. Help! Slow SQL Server perfomance, migrated from Access

12. SQL Perfomance monitor counters issues