0

I have a table in SQL Server 2008 with 553,386 rows and every day it is increasing by about 400 rows. Now when I full scan of the table (Select * from) it is taking 23 sec. Is there any solution to speed up the query?

My PC configuration is, CPU: Intel Core 2 Duo 2.0 GHz, RAM: 4GB, OS: Windows 7 32 bit

Thanks in advance!

Table structure is given below:

USE [dbCPFL_Test]
GO

/****** Object:  Table [dbo].[PriceData]    Script Date: 10/04/2013 19:56:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PriceData](
    [TradeDate] [date] NOT NULL,
    [Ticker] [varchar](50) NOT NULL,
    [ClsPRC] [float] NOT NULL,
    [No_of_Share] [float] NOT NULL,
    [TradeStatus] [char](1) NOT NULL,
    [Adj_MKTCap] [float] NOT NULL,
    [deList] [char](1) NOT NULL,
 CONSTRAINT [PK_PriceData] PRIMARY KEY CLUSTERED 
(
    [TradeDate] ASC,
    [Ticker] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
Improve this question
3
  • 1
    Do you have a filter on the query? If just Select * FROM PriceData you cant speed that up.
    – Magnus
    Commented Oct 4, 2013 at 14:13
  • 4
    Doing a SELECT * and asking for all columns and all rows will pretty much always result in a full table scan (or clustered index scan). Not much you can do to speed that up .... what you could do is (a) select less columns (use SELECT col1, col2, ... FROM ....) and/or (b) select less rows (use a WHERE ..... clause). Then you might be able to get a speed boost with an index.
    – marc_s
    Commented Oct 4, 2013 at 14:23
  • Creating a view of the table. and then query the view rather than directly querying the table. Not sure though :p
    – Irfan TahirKheli
    Commented Oct 4, 2013 at 14:51

2 Answers 2

Reset to default
1

Sure; upgrade your hardware. Note that rendering results in Management Studio is notoriously slow, so keep that in mind if that is your performance benchmark tool. However ...

The big question I have is: why do you care? I'm hard pressed to come up with a legitimate reason to return the entire 550k record set. You're not going to display that much on any screen in an application. No one would ever view it. If you're returning it for post-processing an aggregation or something of the sort, you should be aggregating it in SQL, then returning it. If you are dumping it in an ETL process, long queries basically come with the territory (unless you're clever and come up with an incremental Extraction).

Improve this answer
0

when I full scan of the table .... Is there any solution to speed up [that]

Not that I'm aware of. A full table scan is a full table scan (hopefully it's actually a clustered index scan).

Adjusting a query back from all columns e.g. just the members of the clustered index might make a marginal difference, but there aren't a massive number of columns in that table.

Are you actually seeing 'full table scan' in execution plans? ( if you do this "select * from [dbo].[PriceData] )

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.