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
Can you give us the execution plans for these queries? Also, are you sure that that there is an index on CallStartTimeStamp?
|||
ShawnNWF wrote:
Can you give us the execution plans for these queries? Also, are you sure that that there is an index on CallStartTimeStamp?
For not reason another, No execution plan or anything on the db. But, 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]
These indexes are only on RowID, but you don't reference RowID anywhere in your query, so these aren't of any use in this case.
/Kenneth
|||Why are you using this expression in the "where" clause?
> WHERE CallIDChar = CallIDChar
What about having an index by [CallID]?
AMB|||These indexes are not being used in this query. How many rows are we talking about for this table in total? I am assuming that this is not a small ammoun of data for the query to take 5 mintues.
|||
Why are you using this expression in the "where" clause?
> WHERE CallIDChar = CallIDChar
What about having an index by [CallID]?
AMB
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
These indexes are not being used in this query. How many rows are we talking about for this table in total? I am assuming that this is not a small ammoun of data for the query to take 5 mintues.
select count(*) from CallInfo
result=150490 rows
note: this database also has the voice logs at the same time (table=ImageInfo)
|||Melih Araz,
That is really a bad practice, are you aware of SQL Injection?
You are not only opening the doors to let others inject SQL code into your db, if no, you are bombarding the engine with queries with low probability of being able to reuse the execution plan. Try to parameterize the statement you are sending to the engine.
Here is an outstanding article that will help you with this issue.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
|||hunchback,
Thank you for advice, but our application is closed to internet, also users cannot access to internet.
|||
Melih Araz,
That is really a bad practice, are you aware of SQL Injection?
You are not only opening the doors to let others inject SQL code into your db, if no, you are bombarding the engine with queries with low probability of being able to reuse the execution plan. Try to parameterize the statement you are sending to the engine.
Here is an outstanding article that will help you with this issue.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
Who said that SQL Injection just comes from the internet?
AMB
|||What might be happening is this: There are not enough changes to the table in one day to cause the statistics to be updated. By default, statistics are only updated if a sizeable percentage of rows are inserted. One day in a many-months table is not enough. As as result, when the query optimizer considers the query with today's date, it refers to the statistics and estimates that there are almost zero rows in the table for today's date. Based on this incorrect rowcount estimate, the optimizer chooses a bad query plan.
The optimizer might then decide to begin the query execution by using a nonclustered, noncovering index on CallStartTimeStamp to identify which rows have today's CallStartTimeStamp value. For these rows, a lot of redundant or inefficient work might then be done. If there are only a few rows, it's no big deal.
If it turns out there are many rows for today, the query plan to finish the query may take hundreds, even millions, of times longer than expected, If the optimizer knew there were many rows for today, it would have chosen a more efficient way to handle different parts of the query, because it would be no advantage to filter for CallStartTimeStamp first.
How to fix this? Try running UPDATE STATISTICS on the table. (Your problem may be outdated statistics, not index fragmentation.) You can also try to hide the date strings from SQL Server by making them variables and setting their values before the query.
One more thing. If your data has tenths of a second in the timestamp, you are missing all calls beginning in the last 1/2 second of each day. It is always best to write ranges like this with one >= and one < as follows:
... >= '2007-09-20T00:00:00' AND
... < '2007-09-21T00:00:00'
For the second value, you can also use DATEADD(day,1,<first value>).
Steve Kass
Drew University
http://www.stevekass.com
|||
Who said that SQL Injection just comes from the internet?
AMB
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.
Regards,
Melih
What might be happening is this: There are not enough changes to the table in one day to cause the statistics to be updated. By default, statistics are only updated if a sizeable percentage of rows are inserted. One day in a many-months table is not enough. As as result, when the query optimizer considers the query with today's date, it refers to the statistics and estimates that there are almost zero rows in the table for today's date. Based on this incorrect rowcount estimate, the optimizer chooses a bad query plan.
The optimizer might then decide to begin the query execution by using a nonclustered, noncovering index on CallStartTimeStamp to identify which rows have today's CallStartTimeStamp value. For these rows, a lot of redundant or inefficient work might then be done. If there are only a few rows, it's no big deal.
If it turns out there are many rows for today, the query plan to finish the query may take hundreds, even millions, of times longer than expected, If the optimizer knew there were many rows for today, it would have chosen a more efficient way to handle different parts of the query, because it would be no advantage to filter for CallStartTimeStamp first.
How to fix this? Try running UPDATE STATISTICS on the table. (Your problem may be outdated statistics, not index fragmentation.) You can also try to hide the date strings from SQL Server by making them variables and setting their values before the query.
One more thing. If your data has tenths of a second in the timestamp, you are missing all calls beginning in the last 1/2 second of each day. It is always best to write ranges like this with one >= and one < as follows:
... >= '2007-09-20T00:00:00' AND
... < '2007-09-21T00:00:00'
For the second value, you can also use DATEADD(day,1,<first value>).
Steve Kass
Drew University
http://www.stevekass.com
Steve Kass,
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
By the way, how was the execution plan should be done?
Regards,
Melih
|||
What might be happening is this: There are not enough changes to the table in one day to cause the statistics to be updated. By default, statistics are only updated if a sizeable percentage of rows are inserted. One day in a many-months table is not enough. As as result, when the query optimizer considers the query with today's date, it refers to the statistics and estimates that there are almost zero rows in the table for today's date. Based on this incorrect rowcount estimate, the optimizer chooses a bad query plan.
The optimizer might then decide to begin the query execution by using a nonclustered, noncovering index on CallStartTimeStamp to identify which rows have today's CallStartTimeStamp value. For these rows, a lot of redundant or inefficient work might then be done. If there are only a few rows, it's no big deal.
If it turns out there are many rows for today, the query plan to finish the query may take hundreds, even millions, of times longer than expected, If the optimizer knew there were many rows for today, it would have chosen a more efficient way to handle different parts of the query, because it would be no advantage to filter for CallStartTimeStamp first.
How to fix this? Try running UPDATE STATISTICS on the table. (Your problem may be outdated statistics, not index fragmentation.) You can also try to hide the date strings from SQL Server by making them variables and setting their values before the query.
One more thing. If your data has tenths of a second in the timestamp, you are missing all calls beginning in the last 1/2 second of each day. It is always best to write ranges like this with one >= and one < as follows:
... >= '2007-09-20T00:00:00' AND
... < '2007-09-21T00:00:00'
For the second value, you can also use DATEADD(day,1,<first value>).
Steve Kass
Drew University
http://www.stevekass.com
Hi Steve Kass,
Great! It worked withal old script. Our problem was outdated statistics.
UPDATE STATISTICS CallInfo
UPDATE STATISTICS ImageInfo
GO
Should I run this comment daily?
Thanks&Regards,
Melih
No comments:
Post a Comment