August 10, 2010 at 6:28 am
Hi,
I have a table with 3,000,000 records for one year. It has only a primary key.
Just select query without any where clause is taking 15-20min of time.
My question is, will there be any impact/help in fetching records (just for select * from table1) if i add few more Index on this table.
Thanks in Advance,
Sudhanva
August 10, 2010 at 6:35 am
If you are fetching all the records, then the Indexes will not be used and hence adding an Index will not make a difference.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 10, 2010 at 6:58 am
Selecting all data means either you have clustered index scan or table scan.see the execution plan , picture will be clear to you
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 10, 2010 at 7:42 am
sudhanva (8/10/2010)
Hi,I have a table with 3,000,000 records for one year. It has only a primary key.
Just select query without any where clause is taking 15-20min of time.
My question is, will there be any impact/help in fetching records (just for select * from table1) if i add few more Index on this table.
Thanks in Advance,
Sudhanva
How much of that 15-20min time is streaming the three million rows of data to the client?
How useful is it from a business perspective?
Are there any production (live, business) queries against this table?
Do they appear to run slowly?
Are any columns other than the one which is indexed used in JOINs, WHERE clause?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2010 at 11:51 am
What business need is being satisfied by pulling all the data? Users do not look at anything past a few hundred rows in general. If you're looking at data migration, there are better mechanisms for migrating data when you have to move everything than simply querying the data. A backup and a restore would work better. Or you could use one of the many third-party utilities that can mount the backup as a database and query that independently on the server you're loading to. There are other options I'm not thinking of at the moment. So, my question remains, why are you querying everything?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 11, 2010 at 12:33 am
Guys thanks for your concern.
Grant Fritchey (8/10/2010)
What business need is being satisfied by pulling all the data? .why are you querying everything?
We have created a view using this Table (lets say Table1) joining with some other tables. And this View(lets say View1) is being used in the SSAS to fill the Cube. I got a complaint sayng that the Cube is taking lot of time to process. When I debugged, I found that this View1 is having the culprit.
Please guide.
Thanks,
Sudhanva
August 11, 2010 at 12:39 am
The entire view may need to be instantiated in tempdb.
Try changing to the underlying table.
Alternatively, you could try an indexed view
August 11, 2010 at 1:41 am
Post the view definition?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2010 at 3:04 am
GilaMonster (8/11/2010)
Post the view definition?
CREATE VIEW [View1]
AS
SELECT ROW_NUMBER() OVER (ORDER BY VERSIONID) AS ROW_NUMBER,* FROM
(SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, [value]
from Table1
WHERE PARAMETERNAME in (SELECT convert(varchar(max),decryptbypassphrase('SKey',PARAMETERNAME)) PARAMETERNAME FROM MST_PARAMETER)
) P
PIVOT (SUM(p.[value]) for PARAMETERNAME
in ([Data1],[Data2],[Data3],
[Data4],[Data5],[C Rate],
[Annual Days of Therapy],[Days per R],[Gross Sales (Actual)] ,
[Gross to Net],[Market Access],[Market Treated],
[Patients DOT],[Persistency Rate],[Price/Unit (USD)],
[Scale-up Factor],[Treated],
,[Volume (Std. Units)]
)
)
as pvt
August 11, 2010 at 3:38 am
sudhanva (8/11/2010)
GilaMonster (8/11/2010)
Post the view definition?
CREATE VIEW [View1]AS
SELECT ROW_NUMBER() OVER (ORDER BY VERSIONID) AS ROW_NUMBER,* FROM
(SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, [value]
from Table1
WHERE PARAMETERNAME in (SELECT convert(varchar(max),decryptbypassphrase('SKey',PARAMETERNAME)) PARAMETERNAME FROM MST_PARAMETER)
) P
PIVOT (SUM(p.[value]) for PARAMETERNAME
in ([Data1],[Data2],[Data3],
[Data4],[Data5],[C Rate],
[Annual Days of Therapy],[Days per R],[Gross Sales (Actual)] ,
[Gross to Net],[Market Access],[Market Treated],
[Patients DOT],[Persistency Rate],[Price/Unit (USD)],
[Scale-up Factor],[Treated],
,[Volume (Std. Units)]
)
)
as pvt
You called it "Just select query without any where clause ..."
It is funny :-D:-D:-D
You can do few things to improve it.
1. Don't use '*' but list all columns.
2. Add index on VERSIONID (sorting ASC)
3. You should avoid using "WHERE PARAMETERNAME in(SELECT ... "
Using "IN" and "NOT IN" is not very good in terms of performance.
That should be replaced with JOIN (inner join in your case).
To make it even faster, I would recommend to add non-persistent computed column to MST_PARAMETER. This column should "store" result of "convert(varchar([RequiredLength]),decryptbypassphrase('SKey',PARAMETERNAME)). Do you really need varchar(max) here?
I don't believe so (if you do really need varchar(max), you can't expect that values of upto 2Gb in size will be compared very fast). If you will be able to define the proper size of required varchar value, you will be able to create an index on this column and that would help performance as well.
August 11, 2010 at 4:04 am
You could try preaggregating the source query:
;WITH ParameterList AS (
SELECT convert(varchar(max),decryptbypassphrase('SKey',PARAMETERNAME)) PARAMETERNAME
FROM MST_PARAMETER
),
Preaggregate AS (
SELECT PARAMETERNAME, -- extra columns required here
Col1 = SUM(Col1),
Col2 = SUM(Col2),
Col3 = SUM(Col3),
Col4 = SUM(Col4),
Col5 = SUM(Col5),
Col6 = SUM(Col6),
Col7 = SUM(Col7),
Col8 = SUM(Col8),
[value] = SUM([value])
FROM Table1 t
INNER JOIN ParameterList p ON p.PARAMETERNAME = t.PARAMETERNAME
GROUP BY PARAMETERNAME
)
SELECT ROW_NUMBER() OVER (ORDER BY VERSIONID) AS ROW_NUMBER,
*
FROM
(SELECT PARAMETERNAME, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, [value]
FROM Preaggregate) AS p
PIVOT (SUM(p.[value])
FOR PARAMETERNAME IN
([Data1],[Data2],[Data3],
[Data4],[Data5],[C Rate],
[Annual Days of Therapy],[Days per R],[Gross Sales (Actual)] ,
[Gross to Net],[Market Access],[Market Treated],
[Patients DOT],[Persistency Rate],[Price/Unit (USD)],
[Scale-up Factor],[Treated],
,[Volume (Std. Units)]
)
)
AS pvt
which shows that you could do with an index on the column PARAMETERNAME of Table1.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 12, 2010 at 8:50 am
You should also analyze wait stats and especially IO stalls to find out where the process is bottlenecking. tempdb IO performance issue here anyone? 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply