April 21, 2011 at 4:28 pm
Is there a way to do COUNT(*) to run faster or rewrite alternative which will count number of records for particular select. It is SELECT with 6 joined tables and 460 000 records.
Running search without count(*) is 7ms, with count(*) it jumps up to 360ms ... Drastic difference, however I need to know number of records for particular search.
Thanks
April 21, 2011 at 4:55 pm
i use this when i need a fast result.
SELECT
o.name AS [Name],
i.rowcnt AS [Count]
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id AND indid IN(0,1)
WHERE xtype = 'u'
order by i.rowcnt desc
it is dependent on your statistics being up to date.
April 21, 2011 at 4:56 pm
A little more description if you could between the without/with count differences? I'm trying to grok what the difference in the queries would be, count(*) shouldn't cause that much of a difference in the plans.
If you can, attach the two execution plans as well, the devil might be in the details.
In general... 360 ms for a half a million row count doesn't seem that outrageous to me. 7 ms to return 460,000 rows seems insanely quick, thus why I'd like to know more specifics.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 21, 2011 at 4:56 pm
tjaybelt (4/21/2011)
i use this when i need a fast result.SELECT
o.name AS [Name],
i.rowcnt AS [Count]
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id AND indid IN(0,1)
WHERE xtype = 'u'
order by i.rowcnt desc
it is dependent on your statistics being up to date.
I agree TJ if you're looking for table counts, but he's trying to determine the counts off a joined structure.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 21, 2011 at 5:01 pm
You'll have to provide some more information. A query which counts records shouldn't take longer than the equivalent query that returns them all.
The best ways to speed it up would be to consider indexes, and perhaps considering outer joins with foreign keys. But if you write your queries here, people will be able to help you more.
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
April 21, 2011 at 5:06 pm
I'm going to guess that you only return the first page of data in 7ms, and that this allows a massive shortcut in processing.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
April 22, 2011 at 12:08 am
Here is the code for COUNT:
select @pocet=COUNT(i.id)
from vwShowInzeraty i
left join vwShowMotorizacia m on i.id_motorizacia = m.id
inner join vwShowKategorie k on i.id_kategoria = k.id
where
(@id is null or i.id = @id)
and (@id_kategoria is null or k.position.ToString() like @kategoria_text + '%')
and (@id_znacka is null or i.id_znacka = @id_znacka)
and (@id_model is null or i.id_model = @id_model)
and (@id_karoseria is null or i.id_karoseria = @id_karoseria)
and (@id_palivo is null or i.id_palivo = @id_palivo)
and (m.vykon between @vykon_od and @vykon_do)
and (m.vykon_ps between @vykon_ps_od and @vykon_ps_do)
and (m.objem between @objem_od and @objem_do)
and (i.rok_vyroby between @rok_vyroby_od and @rok_vyroby_do)
and (i.najazdene between @najazdene_od and @najazdene_do)
and (i.cena between @cena_od and @cena_do)
and (@user is null or i. = @user)
Without this COUNT select whole stored procedure returns PAGED data within 55ms (20 records found with WHERE conditions).
With this COUNT select it returns data within 288ms.
Is it possible? Or this is closest I can get with this kind of table.
April 22, 2011 at 12:46 am
I'm guessing there's some nasty logic in those views.
But still, to find twenty rows that satisfy those conditions is going to be a lot quicker than counting how many there are in total.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
April 22, 2011 at 3:29 am
You can also use COUNT(1) instead of COUNT(*) or COUNT(<colname>).
April 22, 2011 at 3:40 am
Using COUNT(1) instead of COUNT(*) won't change the situation.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
April 22, 2011 at 4:14 am
Hello catch-all query (otherwise known as performance nightmare)
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Count(1) is the same as count(*), in meaning and performance. Count(<column name>) is not the same in meaning and can only perform the same speed or slower than count(*)
http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/
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
April 22, 2011 at 4:17 am
Try in the end of complete query
OPTION (MERGE JOIN)
or
OPTION(MAXDOP 2) --Its depend on the available Processors in the Server
create temp table for data you want to filter then join them
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 22, 2011 at 5:09 am
Syed Jahanzaib Bin hassan (4/22/2011)
OPTION (MERGE JOIN)or
OPTION(MAXDOP 2) --Its depend on the available Processors in the Server
Got some justification for those suggestions?
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply