September 20, 2007 at 7:18 am
Hi All,
When I wanted to see ONLY today’s records on the sql server, it took 5 minutes.
When I wanted to see records including today’s records on the sql server, no problem. (19 Sep. is a past date which is working properly)
It is very interesting it takes 5 minutes to sort the lastday there is nothing wrong with the other days.
I ran DBCC INDEXDEFRAG method but no change anything.
I am wondering about something, everyday at 22:00 pm we are getting backup. Is this process creating a difference between before getting the back up and after got the backups?
It is really interesting issue. I do not know what I can say. It seems there is an issue with today’s records. May be there are some locks on the today’s records. I do not know. Why does not cause any issue for old dates but for current day ?
SELECT DISTINCT CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration
FROM (SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration
FROM CallInfo AS MAINTBL
JOIN ( SELECT CallID, DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration
FROM CallInfo
GROUP BY CallID
) AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID
) as CallInfo
WHERE
CallIDChar = CallIDChar
AND
(CallStartTimeStamp >= '2007-09-20T00:00:00')
AND
(CallStartTimeStamp <= '2007-09-20T23:59:59')
What can be reason for this issue and how can I solve it ?
Thank you in advance,
Melih
September 20, 2007 at 7:47 am
I'm not understanding how the call duration is getting you accurate results, but that's a different discussion. A few things jump out at me:
Seems to be you could do this a LOT more efficiently with something like:
SELECT DISTINCT MAINTBL.CallIDChar, MAINTBL.ANI, MAINTBL.DNIS, MAINTBL.CallerName, MAINTBL.AgentName, MAINTBL.CallQueue, MAINTBL.AgentID, MAINTBL.CallType, MAINTBL.CallStartTimeStamp, MAINTBL.CallEndTimeStamp, DRTTable.CallDuration
FROM CallInfo AS MAINTBL
INNER JOIN ( SELECT CallID, DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration
FROM CallInfo
GROUP BY CallID
where
(CallStartTimeStamp >= '2007-09-20T00:00:00')
AND
(CallStartTimeStamp <= '2007-09-20T23:59:59')
) AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID
WHERE
(MAINTBL.CallStartTimeStamp >= '2007-09-20T00:00:00')
AND
(MAINTBL.CallStartTimeStamp <= '2007-09-20T23:59:59')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 20, 2007 at 8:12 am
Hi Matt Miller,
When i run the script below TSQL server turns query form.
Part of code:
Set objImageInfoRS = Server.CreateObject("adodb.recordset")
'Set to use client-side cursor
objImageInfoRS.CursorLocation = 3 'adUseClient
'Set objConn = Server.CreateObject("ADODB.Connection")
'objConn.Open strConnect
strError = openDB(objConn, strConnect)
strSQL = "select distinct CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration"
strSQL = strSQL & " from (" & _
"SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration " & _
"FROM #TABLENAME# AS MAINTBL " & _
"JOIN (SELECT CallID, " & _
"DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration " & _
"FROM #TABLENAME# " & _
"GROUP BY CallID " & _
") AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID " & _
") as CallInfo where CallIDChar = CallIDChar "
If boolAgent Then
strSQL = strSQL & "AND (AgentID='" & strAgentID & "') "
End If
If bAni Then
strSQL = strSQL & "AND (ANI like '" & strAni & "') "
End If
If bDnis Then
strSQL = strSQL & "AND (DNIS like '" & strDnis & "') "
End If
If boolCallType Then
If strCallType = strScriptCallType Then
strSQL = strSQL & "AND (AgentID = '" & "' AND AgentName = '" & "') "
Else
strSQL = strSQL & "AND (CallType = '" & strCallType & "') "
End If
End If
If bCaller Then
strSQL = strSQL & "AND (CallerName like '" & strCaller & "') "
End If
Dim strFrom, strTo, strTmpDateTill
strDateFrom = Session("formElements")("fromYear") & "-" & Session("formElements")("fromMonth") & "-" & Session("formElements")("fromDay")
strDateTill = Session("formElements")("toYear") & "-" & Session("formElements")("toMonth") & "-" & Session("formElements")("toDay")
strTmpDateTill = mid(strDateTill, 1, 11) & " 23:59:59"
strSQL = strSQL & "AND (CallStartTimeStamp >= '" & getStandardDateTime(((Session("ClientTimeBias") * 60) / 86400) + CDate(strDateFrom)) & "') "
strSQL = strSQL & "AND (CallStartTimeStamp <= '" & getStandardDateTime(((Session("ClientTimeBias") * 60) / 86400) + CDate(strTmpDateTill)) & "') "
strSQL = buildLinkedSQL(strSQL, "CallInfo")
'Get call information from CosmoCorder database and eliminate the second segment if the
'call is being monitored so the second segment won't be displayed in the Call Summary.
'Keep a record of each call being monitored and pass it to the Call Detail page so the
'second segment of the call being monitored can also be view.
Dim colMonitorCall
Set colMonitorCall = Server.CreateObject("Scripting.Dictionary")
colMonitorCall.CompareMode = 1 'Case-insensitive comparisons
objImageInfoRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly
September 20, 2007 at 10:44 am
Wow...that's going to hurt. That complicated of a dynamic SQL statement is bound to not do so hot performance-wise. You'll get a LOT more performance if you created a stored proc that did essentially the same thing, so that the execution plan can be optimized and cached, even if the stored proc is building the dynamic SQL. SQL Server is going to start over trying to figure out how best to run this, every time this ADODB record set is instantiated.
Anyway - even without going there - I notice you haven't tried the extra WHERE clause in the sub-query. That will help - a little.
If you don't have one yet, you should get an index on the CALLSTARTTIMESTAMP field. Or - since you're doing grouping AND filtering on it - you might consider creating a computed column which holds only the day portion of that start timestamp, and indexing THAT, then running everything against IT.
what do strani, strDNIS, strAgentID hold? unless they contain partial names WITH wildcards, using the LIKE keyword instead of just = is a bad idea.
Lastly, keep in mind - the kind of syntax you're doing here also opens you up to any number of SQL injection attacks.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 21, 2007 at 4:42 am
Matt Miller,
The account which is used in the application does not have writing authorization.
Database is only for to list and listen the voice records.
Issue is that it takes 5 minutes to sort the lastday there is nothing wrong with the other days. Issue is not security.
By the way, how was the execution plan should be done?
regards,
Melih
September 21, 2007 at 2:34 pm
The purpose of SQL injection attacks is to breach security. Giving someone an open door into getting to data they shouldn't have is usually a problem. it'd be fairly easy to make that query return info from just about any other table, run other queries, perhaps find other user accounts, etc...I call that an issue, but I do understand it's not the issue you're trying to hunt down.
SQL creates an execution plan for everything it needs to run. Once it validates the syntax, it then puts some amount of effort into trying to figure out the least costly way to perform a given script. By that it uses whatever is there to help it run optimally: one of the big items there would be indexing, but there are a fair amount of items going into that. Of course - it's going to use whatever code you give it, so if it gets codes that confuses it or isn't written to take advantage of what it's good at - performance will be poor. It doesn't look like your code is written to take anything other than a brute force approach, so I can't say I'm surprised that it's inconsistent in performance.
What's the clustered index on the table you're querying? Are all of the latest records being written to the end of the file? Are there locks on the file? Are your ADO calls keeping the recordset open during edits? These could easily have some bearing on why the "current day" portion of the dataset takes longer to render.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 22, 2007 at 9:03 am
Update the table's statistics. Out of date stats can result in exactly the problem you describe. Index defrag doesn't update stat, an index rebuild will. Otherwise, run UPDATE STATISTICS
It might take a minute or 2 on a large table.
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
September 24, 2007 at 5:17 am
Matt Miller (21/09/2007)
What's the clustered index on the table you're querying? Are all of the latest records being written to the end of the file? Probably Yes. Are there locks on the file? No. Are your ADO calls keeping the recordset open during edits? I dont know. For, the application service writing to SQL Server. These could easily have some bearing on why the "current day" portion of the dataset takes longer to render.
Matt Miller,
Thank you for detailed and clear information. I changed the query like below and worked! 😉
SELECT DISTINCT CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration
FROM (SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration
FROM CallInfo AS MAINTBL
JOIN ( SELECT CallID, DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration
FROM CallInfo
WHERE (CallStartTimeStamp >= '2007-09-20T00:00:00')
AND (CallStartTimeStamp <= '2007-09-20T23:59:59')
GROUP BY CallID
) AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID
) as CallInfo
Indexes which is working following:
USE [VestelUpgMedia]
GO
/****** Object: Index [PK_CallInfo_RowID] Script Date: 09/20/2007 16:26:42 ******/
ALTER TABLE [dbo].[CallInfo] ADD CONSTRAINT [PK_CallInfo_RowID] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
USE [VestelUpgMedia]
GO
/****** Object: Index [PK_ImageInfo_RowID] Script Date: 09/20/2007 16:28:09 ******/
ALTER TABLE [dbo].[ImageInfo] ADD CONSTRAINT [PK_ImageInfo_RowID] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Regards,
Melih
September 25, 2007 at 8:20 am
Great! It worked withal old script. Our problem was outdated statistics. 😉
UPDATE STATISTICS CallInfo
UPDATE STATISTICS ImageInfo
GO
September 26, 2007 at 8:51 am
Thought so. I have a similar issue that appears from time to time.
What happens is that the stats for a date-time column get out of date and the optimiser thinks there are no rows for today. Hence it produces a plan that's optimal for a very small number of rows. When a large number of rows are actually affected, this plan means that the query runs really slow.
If you ask for a range, then the stats are sufficiently accurate that the optimiser produces a more optimal plan, hence a faster query.
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 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply