to speed up the query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

to speed up the query

Author

Topic

webcakali
Starting Member

16 Posts

Posted - 2011-02-06 : 09:05:11

Declare @tab Table (Gunler int)
Insert Into @tab Values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26),(27),(28),(29),(30),(31)

Select Day(fldKayit) As E,COUNT(fldID) As O From tblFotoLog
Where fldFoto=212 And YEAR(fldKayit)=2010 And MONTH(fldKayit) = 10
Group By Day(fldKayit)

Union All

Select Gunler,0 From @tab
Where Gunler Not In
(Select Distinct Day(fldKayit) From tblFotoLog
Where fldFoto=212 And YEAR(fldKayit)=2010 And MONTH(fldKayit) = 10)
Order By E ASC

This query runs very slowly. There are approximately 5 million records in the table of tblFotoLog.

As can be understood in the query showing the number of records by days.

How can I speed up this query.

Thanks ...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 09:22:09

Is there an index on fldKayit? and an index on fldFoto?

Poor planning on your part does not constitute an emergency on my part.

webcakali
Starting Member

16 Posts

Posted - 2011-02-06 : 09:34:53

yes, there are two

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 10:02:10

and the intent is to produce a zero for each day with no records in the source table?

Not sure if it is faster, but another option..
SELECT daynum,SUM(ISNULL(cnt,0) as cnt
FROM (
Select
Day(fldKayit) As daynum
,COUNT(fldID) As cnt
From
Where fldFoto=212
And fldKayit>='20101001'
And fldkayit < '20101101'
Group By Day(fldKayit)
UNION
SELECT number as daynum
,null
FROM master.dbo.spt_values
Where type='p' and number between 1 and 31
) a
GROUP BY daynum

If there are not time stamps in the date ..this might be faster


SELECT day(date) as daynum,SUM(ISNULL(cnt,0) as cnt
FROM (
Select 
	 fldKayit As date
	,COUNT(fldID) As cnt 
From  
Where fldFoto=212 
		And fldKayit>='20101001' 
		And fldkayit < '20101101' 
Group By Day(fldKayit)
UNION 
SELECT DateAdd(day,number,'20100101') as date
	,null
FROM master.dbo.spt_values
Where type='p' and number between 0 and 30
	) a
GROUP BY daynum

The YEAR(date) and MOnth(date) causes a scan, where as specifying a range on the full date will do a seek which will be faster.

Poor planning on your part does not constitute an emergency on my part.

webcakali
Starting Member

16 Posts

Posted - 2011-02-06 : 10:40:52

Select daynum,SUM(isnull(cnt,0) as cnt)
From (
Select DAY(fldKayit) as daynum,COUNT(fldID) as cnt
From tblFotoLog
Where fldFoto=212
And fldKayit >='20101001'
And fldKayit < '20101101'
Group By DAY(fldKayit)

UNION

Select number as daynum,null
From master.dbo.spt_values Where type='p' and number between 1 and 31) a
Group by daynum

Msg 195, Level 15, State 10, Line 1
'SUM' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'a'.

get error
thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 11:03:19

Sorry..the parenthesis was in the wrong place in the select:

Select daynum,SUM(isnull(cnt,0)) as cnt
From (
Select DAY(fldKayit) as daynum,COUNT(fldID) as cnt
From tblFotoLog
Where fldFoto=212
And fldKayit >='20101001'
And fldKayit < '20101101'
Group By DAY(fldKayit)

UNION

Select number as daynum,null
From master.dbo.spt_values Where type='p' and number between 1 and 31) a
Group by daynum

Poor planning on your part does not constitute an emergency on my part.

webcakali
Starting Member

16 Posts

Posted - 2011-02-06 : 11:11:10

you fantastic ...

was exactly the way I want

Thank you ...

webcakali
Starting Member

16 Posts

Posted - 2011-02-06 : 11:22:57

well
Instead daynum
And fldKayit> = '20101130 '
And fldKayit <'20101231 '
here
Can we print the days between two dates.To be 31 days.

daynum cnt
2010.11.30 0
2010.11.31 0
2010.12.01 0
2010.12.02 0
2010.12.03 0
2010.12.04 0
2010.12.05 0
2010.12.06 0
2010.12.07 0
2010.12.08 0
2010.12.09 0
2010.12.10 0
2010.12.11 0
2010.12.12 0
2010.12.13 0
2010.12.14 0
2010.12.15 0
2010.12.16 0
2010.12.17 0
2010.12.18 0
2010.12.19 0
2010.12.20 0
2010.12.21 0
2010.12.22 0
2010.12.23 0
2010.12.24 0
2010.12.25 0
2010.12.26 0
2010.12.27 0
2010.12.28 0
2010.12.29 0

like this.

thankss

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 12:43:47

Sure, you can even use a parameter and make the < filter 31 days later like below


Declare @startdate datetime
SELECT @Startdate = '20101130'

Select daynum,SUM(isnull(cnt,0)) as cnt
From (
Select DAY(fldKayit) as daynum,COUNT(fldID) as cnt
From tblFotoLog
Where fldFoto=212
And fldKayit >= @startdate
And fldKayit < dateadd(d,31,@startdate)
Group By DAY(fldKayit)

UNION

Select number as daynum,null
From master.dbo.spt_values Where type='p' and number between 1 and 31) a
Group by daynum

Was it loads faster after the suggestion? the index seek makes a monster difference.

Poor planning on your part does not constitute an emergency on my part.

webcakali
Starting Member

16 Posts

Posted - 2011-02-06 : 12:54:38

but no output
1
2
3
4
5

and not in the form of

2010.11.30
2010.11.31
2010.12.01
2010.12.02
2010.12.03
2010.12.04
2010.12.05
2010.12.06

I want to be this way.

"daynum" the first date on the value of the assignment should be done by deadline.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 14:29:46

Sorry, I missed that part..

here is the dateformat you want. Are you also saying no data is being returned at all? Try this one.


Select
	 Date 
	,Cnt=SUM(isnull(cnt,0)) 
From (
		Select REPLACE(convert(char(10),fldKayit,126),'-','.') as date
		,COUNT(fldID) as cnt
		From tblFotoLog
		Where fldFoto=212
		And fldKayit >= @startdate
		And fldKayit < dateadd(d,31,@startdate)
		Group By REPLACE(convert(char(10),fldKayit,126),'-','.')

		UNION ALL

		Select REPLACE(convert(char(10),dateadd(d,number,@startdate),'-','.'),0
		From master.dbo.spt_values Where type='p' and number between 1 and 31) a
GROUP By Date

Poor planning on your part does not constitute an emergency on my part.

webcakali
Starting Member

16 Posts

Posted - 2011-02-06 : 14:56:00

58 line, but turns out the way I want.

I need to return to normal in 31 lines

thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 15:02:04

Sorry formatting of the 2nd half was not right..


Select
	 Date 
	,Cnt=SUM(isnull(cnt,0)) 
From (
		Select REPLACE(convert(char(10),fldKayit,126),'-','.') as date
		,COUNT(fldID) as cnt
		From tblFotoLog
		Where fldFoto=212
		And fldKayit >= @startdate
		And fldKayit < dateadd(d,31,@startdate)
		Group By REPLACE(convert(char(10),fldKayit,126),'-','.')

		UNION ALL

	        Select REPLACE(convert(char(10),dateadd(d,number,@startdate),126),'-','.'),null
		From master.dbo.spt_values Where type='p' and number between 0 and 30) a
GROUP By Date

Poor planning on your part does not constitute an emergency on my part.

webcakali
Starting Member

16 Posts

Posted - 2011-02-06 : 15:11:29

very thank you for your help me "dataguru1971"

worked as I want to query

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 16:33:21

You are welcome.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources