SQL Server Performance Forum – Threads Archive
How i fasten Query
hi My Query is as follow select count(*)from
……………..
…………….. I am useing count(*0 because i have consider null values also This Query took a lot of time to execute as particular table have lot of records. is there any technique to improve performance without applying index? Thanx in advance SQL is love —- More Detail what I need Actually I am fresh programmer I am in IT as 3 months and datbases is my interested subject. Now, Let me review the problem again Why I want to avoid indexes for following reason. 1. Actullay Daily my program truncate table and insert records into table again
(The program is actaully extraction of data from sybase to SQl server) 2. Now suppose i create index on table then what i think is that server has to take
care for index maintaince?(As DATA Modifaction is disadvantage with index) Regards,
Sanjay
How many rows are in the table? Frank
http://www.insidesql.de
at around 50000
SQL is love
Not that much to bring SQL Server to its knees [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br /><pre><br />USE IMM<br />GO<br />SELECT COUNT(*) FROM mails_header<br /></pre><br /><br />yields<br /><pre><br />———– <br />61552<br /><br />(1 row(s) affected)<br /></pre><br /><br />Execution plan looks like<br /><pre><br />StmtText <br />——————————————————————————— <br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />[Expr1002]=Convert([Expr1003])))<br /> |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]=Count(*)))<br /> |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[IMM].[dbo].[mails_header].[PK_mails_header]))<br /></pre><br /><br />in my case an index scan is used.<br />Any reason why you don’t want to apply an index.<br /><br />I would suggest running the Index Tuning Wizard and see what he comes up with<br /><br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Forgot to post SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 14 ms. Frank
http://www.insidesql.de
Hi sanjoo, are you saying that your table has no indexes at all? Cheers
Twan
If you do have an index you could use select count(indexed_column). And if it is a non clustered index, it should be done fast becasue it will only scan the index pages. Bambola.
I think the question is answered. He mailed me directly
quote:
Hello FrankKalis You received the following message from: sanjoo ([email protected]) At:http://www.sql-server-performance.com/forum/ Thanx
I will use index in such case. regards
Sanjoo
Hello FrankKalis You received the following message from: sanjoo ([email protected]) At:http://www.sql-server-performance.com/forum/ Thanx
I will use index in such case. regards
Sanjoo
Frank
http://www.insidesql.de
I have quite huge tables in the web stats database (tens of millions before periodic purge). The fastest way to get the number of rows on such tables is to use sp_spaceused ‘table_name’ Sometimes the row count might be off a bit, so if you want realtime, accurate answer, you need to use SELECT COUNT.
An sp_spaceused after a DBCC Updateusage normally gives you accruate results.
If you don’t want use any index, may be there is some statistics to help.
Run Profiler and see if suggest any statistics. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Again he mailed me directly
quote:
Hi Frank
Thanks for your help. Actually I am fresh programmer I am in IT as 3 months and datbases is my interested subject. Now, Let me review the problem again Why I want to avoid indexes for following reason. 1. Actullay Daily my program truncate table and insert records into table again
(The program is actaully extraction of data from sybase to SQl server) 2. Now suppose i create index on table then what i think is that server has to take
care for index maintaince?(As DATA Modifaction is disadvantage with index) Regards,
Sanjay
Hi Frank
Thanks for your help. Actually I am fresh programmer I am in IT as 3 months and datbases is my interested subject. Now, Let me review the problem again Why I want to avoid indexes for following reason. 1. Actullay Daily my program truncate table and insert records into table again
(The program is actaully extraction of data from sybase to SQl server) 2. Now suppose i create index on table then what i think is that server has to take
care for index maintaince?(As DATA Modifaction is disadvantage with index) Regards,
Sanjay
My answer was
quote:
Hi there, surely you are right.
Indexes have to be maintained and this *might* affect performance if you have a lot of indexes on a table.
But the benefits of having indexes should in almost any case outweight the cost by far. And please post to the forum. It is always helpful to hear more than one opinion and decide than. Take care
Frank
Hi there, surely you are right.
Indexes have to be maintained and this *might* affect performance if you have a lot of indexes on a table.
But the benefits of having indexes should in almost any case outweight the cost by far. And please post to the forum. It is always helpful to hear more than one opinion and decide than. Take care
Frank
Frank
http://www.insidesql.de
And if you have such a huge number of rows that you are trying to insert into a table, you can drop the indexes before the insert and re-create them after insert. This is going to give you better performance during the insert and also with the queries after the insert.
Thank You all who helped me.
SQL is love
]]>