• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Performance Tuning table with over 3 Mil records

Hello all.  I have a table that has over 3 million records in it.  I have an ASP based application that is starting to drag based on the table being so large.  I have stored procedures that hit the table that I think are just taking a long time to run now that so many records are there.  I have run SQL profiler on many occassions and used the Index Tuning Wizard to help and it has before but now I ran it again and there are no suggestions after running a thourough run of it.  Any other suggestions to speed things up on this DB table?  Thanks all
0
sbornstein2
Asked:
sbornstein2
1 Solution
 
rafranciscoCommented:
Please post some sample queries that you perform.  If possible, post your stored procedures to give us an idea on how to improve on its performance.  How many records does the stored procedure usually return?
0
 
ultrapascalCommented:
Some relevant information .

The script of the Table to see the fields and stored procedure.

To look if you have indexes clustered, to see the restrictions
of your stored procedure maybe if you use date ,convert to julian date(only for search).
Partition Table(some servers,or some tables with a range of transactions in the same server).

What kind of server the characteristics and Expected time to make the search.

If you show the table is more easy to make evaluation.

Best Regards
Henry Linux

0
 
sbornstein2Author Commented:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AVAILABLES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AVAILABLES]
GO

CREATE TABLE [dbo].[AVAILABLES] (
      [PART_NO] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [MF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [QTY] [int] NOT NULL ,
      [PRICE] [money] NULL ,
      [DateOfferred] [datetime] NOT NULL ,
      [CO_ID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [internalpartno] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [AV_ID] [int] IDENTITY (1, 1) NOT NULL ,
      [msrepl_tran_version] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [IX_AVAILABLES_PART_NO] ON [dbo].[AVAILABLES]([PART_NO], [MF], [QTY], [DateOfferred]) WITH  FILLFACTOR = 93 ON [PRIMARY]
GO

ALTER TABLE [dbo].[AVAILABLES] ADD
      CONSTRAINT [DF_AVAILABLES_DateOfferred] DEFAULT (getdate()) FOR [DateOfferred],
      CONSTRAINT [DF__AVAILABLE__msrep__04508AFF] DEFAULT (newid()) FOR [msrepl_tran_version],
      CONSTRAINT [PK_AVAILABLES] PRIMARY KEY  NONCLUSTERED
      (
            [AV_ID]
      ) WITH  FILLFACTOR = 93  ON [PRIMARY]
GO

 CREATE  INDEX [IX_AVAILABLES_AVID] ON [dbo].[AVAILABLES]([AV_ID]) WITH  FILLFACTOR = 93 ON [PRIMARY]
GO

 CREATE  INDEX [Internal_Index] ON [dbo].[AVAILABLES]([internalpartno]) WITH  FILLFACTOR = 93 ON [PRIMARY]
GO

 CREATE  INDEX [IX_AVAILABLES] ON [dbo].[AVAILABLES]([CO_ID]) WITH  FILLFACTOR = 93 ON [PRIMARY]
GO

 CREATE  INDEX [IX_AVAILABLES_1] ON [dbo].[AVAILABLES]([AV_ID], [CO_ID]) WITH  FILLFACTOR = 93 ON [PRIMARY]
GO

 CREATE  INDEX [IX_AVAILABLES_2] ON [dbo].[AVAILABLES]([CO_ID], [PART_NO], [AV_ID]) WITH  FILLFACTOR = 93 ON [PRIMARY]
GO

CREATE PROCEDURE spListAvailablesExact @search varchar(40), @av_id int,@Excess int
AS
if @Excess = 1
Begin
select av_id, Part_No, MF, DC,
 Qty = ISNULL(qty, 0),
 Price = ISNULL(price,0), DateOfferred, Co_ID,internalpartno
 from Availables where av_id in (select top 201 Availables.av_id from availables  INNER JOIN CONTACT ON AVAILABLES.CO_ID = CONTACT.co_id where Contact.Excess = 1 And Part_no like @search + '%' and Availables.av_id > @av_id
 order by Availables.av_id)
order by Part_no asc, DateOfferred desc, MF asc, qty desc
End
Else
Begin
select av_id, Part_No, MF, DC,
 Qty = ISNULL(qty, 0),
 Price = ISNULL(price,0), DateOfferred, Co_ID,internalpartno
 from Availables where av_id in (select top 201 av_id from availables where Part_no like @search + '%' and av_id > @av_id
 order by av_id)
order by Part_no asc, DateOfferred desc, MF asc, qty desc
End

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ultrapascalCommented:
I create your table and indexes and trying to understand the stored.

This your stored procedure.

DROP PROCEDURE spListAvailablesExact
GO

CREATE PROCEDURE  spListAvailablesExact
          @strSearch VARCHAR(40)   ,
          @iAv_Id    INT                    ,
          @iExcess   INT
AS

IF @iExcess = 1
BEGIN

        SELECT
                 av_id                            ,
                 Part_No                        ,
                 MF                               ,
                 DC                               ,
                 Qty   = ISNULL(qty, 0)    ,
                 Price = ISNULL(price,0)  ,
                 DateOfferred                 ,
                 Co_ID                           ,
                 internalpartno
        FROM
                 Availables
        WHERE
                 av_id
                      IN
                      (
                            SELECT TOP 201
                                            Availables.av_id
                            FROM  availables  
                            INNER JOIN CONTACT ON AVAILABLES.CO_ID = CONTACT.co_id
                            WHERE
                                   Contact.Excess = 1                    AND
                                   Part_no like @strSearch + '%'    AND
                                   Availables.av_id > @iAv_Id
                            ORDER BY Availables.av_id
                      )
        ORDER BY
                  Part_no            ASC  ,
                  DateOfferred    DESC ,
                  MF                  ASC  ,
                  qty                  DESC
END
ELSE
BEGIN

       SELECT
                  av_id                                ,
                  Part_No                            ,  
                  MF                                   ,
                  DC                                   ,
                  Qty   = ISNULL(qty, 0)        ,
                  Price = ISNULL(price,0)      ,
                  DateOfferred                     ,
                  Co_ID                               ,
                  internalpartno
      FROM
                  Availables
      WHERE
                  av_id
                        IN
                        (
                              SELECT TOP 201
                                     av_id
                              FROM
                                     availables
                              WHERE
                                     Part_no like @strSearch + '%' and
                                     av_id     > @iAv_Id
                              ORDER BY
                                       av_id
                        )
      ORDER BY
                Part_no ASC, DateOfferred DESC, MF ASC, qty DESC
END



Questions:
1.-
    It is 100% necessary use the lock in tables?
    The request of information is 100 necessary get the last status of parts at the moment
    and lock the rows to do not use by other people.Because if you get the information and
    in some minutes after, change the values not is the last.

   I suggest first  :   Availables(nolock)
          but it thinking in the air because i dont know how is the process or request or the
         fidelity of the information required

2.-Maybe is Posible change the IN(subquery)   by a inner join(query) is only to look how is working your
query.

3.- I dont know if the Part_no maybe your issue of time is the part_no because is
a string and are scanning 3 millions to found the first 201 records.

maybe one extra index like
that
  [PART_NO], [AV_ID],[CO_ID]
because the principal problem are the strings are more difficult make a comparison of string.
the integer are more fast.

4.-Its neccesary evaluate if you are using all indexes because that is other factor(slow)
when you will be making a insert or update over fields of the indexes


You need a troubleshooting run the query part by part to found where is the leak of time.
and try to implement other kind of search or create index clustered by the field of the problem.

And evaluate how much people is using the database in the moment,your current memory.
Stop your Test Enviroment and look how is increased after run only that query(part by part)
and stop the server in any time what you will be running a modification of your query.Maybe
you need more resources of the pc or increase the locks of the Database sp_configure.

l

Best Regards,
Henry Linux
0
 
ultrapascalCommented:
i was thinking 2 extra things

1.-if you check the size of the varchar and change
like by substring

sometimes work more fast like other times substring is
only to test to evaluate(i dont know when is more fast substring or when like
maybe is related with the char and varchar).

and im thiking in use temporal tables break in 2 parts the process.
 and the second part is the more big will be looking in all table for table product with
x av_id

regards
0
 
PUMASOFTCommented:
Are any of these any godd

CREATE PROCEDURE spListAvailablesExact @search varchar(40), @av_id int,@Excess int
AS
select TOP 201 av_id, Part_No, MF, DC,
 Qty = ISNULL(qty, 0),
 Price = ISNULL(price,0), DateOfferred, Co_ID,internalpartno
from Availables AS A
INNER JOIN CONTACT AS C ON A.CO_ID = C.co_id  
where (@Excess <> 1 or C.Excess = @Excess) And Part_no like @search + '%' and A.av_id > @av_id
order by Part_no asc, DateOfferred desc, MF asc, qty desc




CREATE PROCEDURE spListAvailablesExact @search varchar(40), @av_id int,@Excess int
AS
select * from (
    select TOP 201 av_id, Part_No, MF, DC,
     Qty = ISNULL(qty, 0),
     Price = ISNULL(price,0), DateOfferred, Co_ID,internalpartno
    from Availables AS A
    INNER JOIN CONTACT AS C ON A.CO_ID = C.co_id  
    where (@Excess <> 1 or C.Excess = @Excess) And Part_no like @search + '%' and A.av_id > @av_id) as x
order by Part_no asc, DateOfferred desc, MF asc, qty desc


CREATE PROCEDURE spListAvailablesExact @search varchar(40), @av_id int,@Excess int
AS

set rowcount = 201--this could then be set by a parameter

select av_id, Part_No, MF, DC,
 Qty = ISNULL(qty, 0),
 Price = ISNULL(price,0), DateOfferred, Co_ID,internalpartno
from Availables AS A
INNER JOIN CONTACT AS C ON A.CO_ID = C.co_id  
where (@Excess <> 1 or C.Excess = @Excess) And Part_no like @search + '%' and A.av_id > @av_id
order by Part_no asc, DateOfferred desc, MF asc, qty desc

0
 
mastooCommented:
Can I toss in that you need to be sure to update statistics (or better yet rebuild indexes) if any significant amount of data in the table has changed or been added?
0
 
arbertCommented:
Just another recomendation:

[MF] [varchar] (2)
and
[DC] [varchar] (4)

You should think about redefining these as char.  The overhead for varchar negates any benefit on data of this length....

Can you also post what your query plans look like.

Brett
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now