SQL SCRIPT
-
Hi Guys, I am having problem when running the Following SQL script.It takes Pretty long time to return result. I am having about 500,000 records in my database. select count(distinct(CR_Cli)),SO_name from Callrecords inner join dbo.StudioOperators On CR_StudioOperatorID=SO_ID where datename(month,cr_callstart)='April' and Cr_cli not in (select Cr_cli from Callrecords where cr_callstart<'2009-03-31') group by SO_name desc Is there any way i can write this script? Thanks you very much.
-
Hi Guys, I am having problem when running the Following SQL script.It takes Pretty long time to return result. I am having about 500,000 records in my database. select count(distinct(CR_Cli)),SO_name from Callrecords inner join dbo.StudioOperators On CR_StudioOperatorID=SO_ID where datename(month,cr_callstart)='April' and Cr_cli not in (select Cr_cli from Callrecords where cr_callstart<'2009-03-31') group by SO_name desc Is there any way i can write this script? Thanks you very much.
You do realise that there are several Database related fora on CP? This is a C# forum.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
You do realise that there are several Database related fora on CP? This is a C# forum.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
How about the "General Database" forum??
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
General Database might be better, if it is MS SQLServer.
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
Hi Guys, I am having problem when running the Following SQL script.It takes Pretty long time to return result. I am having about 500,000 records in my database. select count(distinct(CR_Cli)),SO_name from Callrecords inner join dbo.StudioOperators On CR_StudioOperatorID=SO_ID where datename(month,cr_callstart)='April' and Cr_cli not in (select Cr_cli from Callrecords where cr_callstart<'2009-03-31') group by SO_name desc Is there any way i can write this script? Thanks you very much.
It´s very hard to rewrite a SQL query that I didn´t know the schema and relationships. Anyway, I made some changes.
kibromg wrote:
select count(distinct(CR_Cli))
Verify if the column has an index.
kibromg wrote:
datename(month,cr_callstart)='April'
Do not use much functions on where clause. Simplify... use "month(CR.callstart) = 4".
kibromg wrote:
and Cr_cli not in (select Cr_cli from Callrecords where cr_callstart<'2009-03-31')
If Cr_Cli "not in" where cr_callstart < '2009-03-31 then Cr_Cli "is in" where cr_callstart > '2009-03-30'. More than that, you are already referencing Callrecords in inner join, so you don´t need to create a subselect. On where clause, use this condition: "CR.callstart > '2009-03-30'".
kibromg wrote:
group by SO_name desc
On group by you have the column SO_name. Does this column have an index?! If not, you must consider creating one. Try the code below, maybe it helps.
select count(distinct(CR.Cli)) , SO.name from Callrecords CR inner join StudioOperators SO on CR.StudioOperatorID = SO.ID where month(CR.callstart) = 4 and CR.callstart > '2009-03-30' group by SO.name desc