asked on

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

rafrancisco

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?

ultrapascal

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

sbornstein2

ASKER

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

ASKER CERTIFIED SOLUTION

ultrapascal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ultrapascal

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

PUMASOFT

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

mastoo

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?

arbert

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