Real Time DSS: February 2012

Saturday, February 25, 2012

Really interesting issue!

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.

|||

hunchback wrote:

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

|||

ShawnNWF wrote:

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.

hunchback wrote:

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

|||

hunchback wrote:

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

|||

Steve Kass wrote:

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

|||

Steve Kass wrote:

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

Should I run this comment daily?

Thanks&Regards,

Melih

Really interesting issue!

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?

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:

|||

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.

|||

hunchback wrote:

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:

strSQL = buildLinkedSQL(strSQL, "CallInfo")

|||

ShawnNWF wrote:

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?

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.

hunchback wrote:

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

|||

... >= '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

|||

hunchback wrote:

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

|||

Steve Kass wrote:

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

|||

Steve Kass wrote:

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

Should I run this comment daily?

Thanks&Regards,

Melih

Really interesting issue!

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?

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:

|||

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.

|||

hunchback wrote:

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:

strSQL = buildLinkedSQL(strSQL, "CallInfo")

|||

ShawnNWF wrote:

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?

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.

hunchback wrote:

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

|||

... >= '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

|||

hunchback wrote:

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

|||

Steve Kass wrote:

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

|||

Steve Kass wrote:

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

Should I run this comment daily?

Thanks&Regards,

Melih

Really interesting issue!

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?

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:

|||

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.

|||

hunchback wrote:

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:

strSQL = buildLinkedSQL(strSQL, "CallInfo")

|||

ShawnNWF wrote:

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?

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.

hunchback wrote:

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

|||

... >= '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

|||

hunchback wrote:

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

|||

Steve Kass wrote:

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

|||

Steve Kass wrote:

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

Should I run this comment daily?

Thanks&Regards,

Melih

Really interesting behaviour of ADO

Hell all,
I am inserting record with times when SP begins and ends
and I log times when ADO.command.exec starts and ends
I've noticed that there are situations where SP reports 13 ms from begin to end
and ADO reports 3 seconds from begin to end. It happens about 3 times per 2000 tries.
Note: There are no resultsets, only OUTPUT params.
SO it looks to me either ADO glitch or network or what?
Does someone had something like this?
Are you accounting for the time it takes to open a connection to the
database, and then get results back from SQL Server?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in message
news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
Hell all,
I am inserting record with times when SP begins and ends
and I log times when ADO.command.exec starts and ends
I've noticed that there are situations where SP reports 13 ms from begin to
end
and ADO reports 3 seconds from begin to end. It happens about 3 times per
2000 tries.
Note: There are no resultsets, only OUTPUT params.
SO it looks to me either ADO glitch or network or what?
Does someone had something like this?
|||1. No I do not, I have connection open, it is just straight execute of SP.
2. There are no resultsets back to client, only OUTPUT Params, the rest 1998 cases have SP and ADO with almost the same time.
"Narayana Vyas Kondreddi" wrote:

> Are you accounting for the time it takes to open a connection to the
> database, and then get results back from SQL Server?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in message
> news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
> Hell all,
> I am inserting record with times when SP begins and ends
> and I log times when ADO.command.exec starts and ends
> I've noticed that there are situations where SP reports 13 ms from begin to
> end
> and ADO reports 3 seconds from begin to end. It happens about 3 times per
> 2000 tries.
> Note: There are no resultsets, only OUTPUT params.
> SO it looks to me either ADO glitch or network or what?
> Does someone had something like this?
>
>
|||Are you accounting for the time it takes to open a connection to the
database, and then get results back from SQL Server?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in message
news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
Hell all,
I am inserting record with times when SP begins and ends
and I log times when ADO.command.exec starts and ends
I've noticed that there are situations where SP reports 13 ms from begin to
end
and ADO reports 3 seconds from begin to end. It happens about 3 times per
2000 tries.
Note: There are no resultsets, only OUTPUT params.
SO it looks to me either ADO glitch or network or what?
Does someone had something like this?
|||1. No I do not, I have connection open, it is just straight execute of SP.
2. There are no resultsets back to client, only OUTPUT Params, the rest 1998 cases have SP and ADO with almost the same time.
"Narayana Vyas Kondreddi" wrote:

> Are you accounting for the time it takes to open a connection to the
> database, and then get results back from SQL Server?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in message
> news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
> Hell all,
> I am inserting record with times when SP begins and ends
> and I log times when ADO.command.exec starts and ends
> I've noticed that there are situations where SP reports 13 ms from begin to
> end
> and ADO reports 3 seconds from begin to end. It happens about 3 times per
> 2000 tries.
> Note: There are no resultsets, only OUTPUT params.
> SO it looks to me either ADO glitch or network or what?
> Does someone had something like this?
>
>

Really interesting behaviour of ADO

Really important, i need your help!

Hi, i need to know if in RS2000 i can:
1 - Change the text of the exportar link to an image or another text.
2 - Chage de arrangement of the parameters to vertical (one below another)
3- Can i add tooltips for the report parameters?
This is really important for me.
Thanks a lot.What's an "exportar" link?
Mike G.
"Carlos López." <CarlosLpez@.discussions.microsoft.com> wrote in message
news:8FB4B886-0871-4BFA-BDA8-0CAB9D67D82D@.microsoft.com...
> Hi, i need to know if in RS2000 i can:
> 1 - Change the text of the exportar link to an image or another text.
> 2 - Chage de arrangement of the parameters to vertical (one below another)
> 3- Can i add tooltips for the report parameters?
> This is really important for me.
> Thanks a lot.
>|||In the toolbar there's a link of name "Export" sorry :-s
"Mike G." wrote:
> What's an "exportar" link?
> Mike G.
>
> "Carlos LÃ³pez." <CarlosLpez@.discussions.microsoft.com> wrote in message
> news:8FB4B886-0871-4BFA-BDA8-0CAB9D67D82D@.microsoft.com...
> > Hi, i need to know if in RS2000 i can:
> > 1 - Change the text of the exportar link to an image or another text.
> > 2 - Chage de arrangement of the parameters to vertical (one below another)
> > 3- Can i add tooltips for the report parameters?
> >
> > This is really important for me.
> > Thanks a lot.
> >
> >
>
>|||If you're talking about modifying the toolbar, it might not be as easy. But
you can create your own page submit it to reporting server using URL method.
go ahead and google URL Access SQL Reporting Services.
"Carlos LÃ³pez." wrote:
> Hi, i need to know if in RS2000 i can:
> 1 - Change the text of the exportar link to an image or another text.
> 2 - Chage de arrangement of the parameters to vertical (one below another)
> 3- Can i add tooltips for the report parameters?
> This is really important for me.
> Thanks a lot.
>

Really easy/dumb SQL question...

Pardon me, my SQL knowledge is not advanced enough to know how to do this, though I'm sure it's pretty simple:

Let's say I have a table called products, with fields like SKU, name, price. And let's say I have a temporary table with changes to be made (updates) to the current products.. same fields, SKU, name, price. I basically would like to be able to update currently existing entries in the products table, with the changes shown in the temporary table. Example:

PRODUCTS:
T231,Crazy Stick,4.99
023J87,Basketball Hoop,12.99
GB-572,CD Rack,8.99

TEMP. TABLE:
GB-572,Wooden CD Rack,8.99
T231,Crazy Stick,3.95

So I'd like to just merge the products in temp table w/ the ones in products. How can I do this?

Thanks!INSERT INTO PRODUCTS (SKU, name, price, etc)
SELECT SKU, name, price, etc
FROM #TEMP_TABLE|||But wouldn't that insert them as new items, ignoring the current entries? I'd like to update the existing entries in the Products table...|||Oh... well you need to do an UPDATE statement then. Naturally there has to be some sort of common key involved. I assumed you needed an INSERT statement as there was no common key.

General syntax:

UPDATE yourTable
SET yourTable.yourField = #temp.yourField, yourTable.field2 = #temp.field2
FROM yourTable INNER JOIN #temp on yourTable.key=#temp.key|||Ahhh.. thanks a ton!!

Really easy stored proc question

I suppose it's not possible to return a varchar value from a stored proc? I keep getting a conversion error.
How can I return a varchar value, then?
Thanks.. sorry for the stupid question. I did some searching on Google and didn't find much.In the procedure - RAISERROR('your_text', <level int>, <state int>), in your front-end code - interrogate errors collection.|||Originally posted by MDesigner
I suppose it's not possible to return a varchar value from a stored proc? I keep getting a conversion error.

How can I return a varchar value, then?

Thanks.. sorry for the stupid question. I did some searching on Google and didn't find much.

Sounds like you want to use an output parameter in your stored proc

Search SQL Server help on 'output parameters'|||That's right...return can only return an int...use return only to determine the success or failure of a sproc...

use output variable to return values...like:

CREATE PROC mySproc99 @.inputParam varchar(10), @.outputParam varchar(255) OUTPUT
AS|||Originally posted by rdjabarov
In the procedure - RAISERROR('your_text', <level int>, <state int>), in your front-end code - interrogate errors collection.

Hey .. what are you talking about here !!!:confused: :confused: :confused:

Really dumb Stored Proc Question. But I need help and Im lost.

Right now, I'm working on the database of a new project. The project is basically a keyword tracking and referral tracking website directory. My plan is to give the website some java code that will allow me to grab the http_referer and request_uri per page load. (everytime they get traffic)

Right now, I've got some tables I think will work fine. One is the storage table, where I'll store what the javascript grabs.

table.http_ref (columns will be)
uid (Unique ID)
http_referer
request_uri

Then I got the results page I want to parse the http_ref table into. For example.

Here we have a google results page which is a search for baby shoes. This would be the http_referer

http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoesAnd if someone clicks on a link get this page, which let's say I'm tracking in my project.
http://www.thebabymarketplace.com/securestore/c54581.2.html
in my http_ref table I will have
uid
(whatever)
http_referer
http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
request_uri
http://www.thebabymarketplace.com/securestore/c54581.2.html
so I want a stored proc to grab the http_referer column and parse it for the refering search engine
http://www.google.com
and the key words
search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
Then grab the uri_request, which should be the web page visited,
and INSERT into another table where I will hold
keywords
referer
refered page
Any suggestions? I've been going nuts over this.

Then the key point is to parse the http_referer. You should gurantee that all http_referer strings are in a same format, for example the search string begins with '/search?' and the keywords start with '&q=' and locate the end of the string. Then you can use such procedure:

CREATE PROC sp_ParseHttpRef
AS
INSERT INTO Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM Tbl_SourceHttp
go

|||

Thank you very much.

So, can I take this proc and use it as a template to track other search engine referer strings?

For instance:

MSN

http://search.msn.com/results.aspx?q=baby+shoes&FORM=SSRE

or Yahoo

http://search.yahoo.com/search?p=baby+shoes&sm=Yahoo%21+Search&fr=FP-tab-web-t&toggle=1&cop=&ei=UTF-8

and create a Proc for each search engine I'm tracking?

Seems as if I would then have to Schedule the proc's to run, our just call them when I grab the http_referer and request_uri?

|||

Sure you can use the sample as a template, just keep in mind to parse the http_referer correctly. You do not need to schedule the proc to run, you can call it when you grab the http_refer and request_uri in your application, or you can make the proc as an insert trigger on the table which stores the http_refer and request_uri. For example:

create trigger trg_ParseHttp on Tbl_SourceHttp for insert
as
insert into Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM inserted
go

Really Daring SQL-Query: with Cows having fun!

Hi,
For my new application I need some really daring and special query, and i
don't know how to do this the best way. I hope somebody can help me.
The situation:
I have 2 tables:
tblMyCows: table with all my cows in it
CowID: unique ID of a Cow
CowName: name of my cow etc
tblCowHadIntercourse: table that links the Cows that had intercourse with
each other
Cow1ID: ID of the first Cow
Cow2ID: ID of the second Cow
-> so Cow1ID and Cow2ID had intercourse with each other :-)
Cows always have intercourse with another cow, and only one Cow at a time,
but 1 Cow can have intercourse with any other Cow.
now comes the Problem:
Suddenly one of my cows has a disease (no wonder of they have intercourse
like that!). And I need to know the CowID's of all the cows that may have
this disease also. So I need:
- all the CowID's of the Cows that had intercourse with the sick Cow = cow
group 1
- all the CowID's of the Cows that had intercourse with the Cows my sick cow
had intercourse
with
- all the CowID's of the Cows that had intercourse with the Cows that had
intercourse with
the Cows my sick cow had intercourse with
- ...
So basicly it should give me all the potential victims of the disease.
I hope I explained the problem good enough. And I really hope somebody can
help me with this, I'm really stuck on it! Note that each cow can be in
Cow1ID and in Cow2ID for another record...
Thanks a lot in advance,
Pieter
I think found it already myself, thanks to this solution:
http://www.experts-exchange.com/Data..._20034311.html
Although I had to modify ot because I don't work with a parent-row or simple
tree-structure, but with an even relationship (as like every branch could be
of several tee's...). Can anybody confirm me that this works correct? It
seems so after some tests...
**************************
drop table #b
create table #b (ID integer, level int, lr int)
declare @.i int
select @.i = 1
insert #b select 1, 1, 3
while @.@.rowcount <> 0
begin
select @.i = @.i + 1
insert #b
select distinct Cow2ID, @.i, 0
from tblCowHadIntercourse, #b
where Cow1ID = #b.id
and Cow2ID not in (select id from #b where (ID = Cow2ID) and (lr = 0))
insert #b
select distinct Cow1ID, @.i, 1
from tblCowHadIntercourse, #b
where Cow2ID = #b.id
and Cow1ID not in (select id from #b where (ID = Cow1ID) and (lr = 1))
end
select distinct id
from #b
order by id
*******************************
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23yx%23RAKeFHA.2700@.tk2msftngp13.phx.gbl...
> Hi,
> For my new application I need some really daring and special query, and i
> don't know how to do this the best way. I hope somebody can help me.
> The situation:
> I have 2 tables:
> tblMyCows: table with all my cows in it
> --
> CowID: unique ID of a Cow
> CowName: name of my cow etc
> tblCowHadIntercourse: table that links the Cows that had intercourse with
> each other
> --
> Cow1ID: ID of the first Cow
> Cow2ID: ID of the second Cow
> -> so Cow1ID and Cow2ID had intercourse with each other :-)
> Cows always have intercourse with another cow, and only one Cow at a time,
> but 1 Cow can have intercourse with any other Cow.
> now comes the Problem:
> Suddenly one of my cows has a disease (no wonder of they have intercourse
> like that!). And I need to know the CowID's of all the cows that may have
> this disease also. So I need:
> - all the CowID's of the Cows that had intercourse with the sick Cow = cow
> group 1
> - all the CowID's of the Cows that had intercourse with the Cows my sick
cow
> had intercourse
> with
> - all the CowID's of the Cows that had intercourse with the Cows that had
> intercourse with
> the Cows my sick cow had intercourse with
> - ...
> So basicly it should give me all the potential victims of the disease.
> I hope I explained the problem good enough. And I really hope somebody can
> help me with this, I'm really stuck on it! Note that each cow can be in
> Cow1ID and in Cow2ID for another record...
> Thanks a lot in advance,
> Pieter
>
>
|||I really don't know. After a short break I oticed that I did some things
wrong. My new solution:
*************************
create table #b (ID integer, level int)
declare @.i int
select @.i = 1
insert #b select 1, 1
while @.@.rowcount <> 0
begin
select @.i = @.i + 1
insert #b
select distinct Cow2ID, @.i
from tblCowLink, #b
where Cow1ID = #b.id
and Cow2ID not in (select id from #b)
union
select distinct Cow1ID, @.i
from tblCowLink, #b
where Cow2ID = #b.id
and Cow1ID not in (select id from #b)
end
select distinct id
from #b
order by id
*************************
I'm kind of sure it works good. although it is a little slower of the other
one, but I don't think the other one is really 100% correct (although I
can't find out what could be wrong with it and the results are always ok)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:eYJyCHLeFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I think found it already myself, thanks to this solution:
>
http://www.experts-exchange.com/Data..._20034311.html
> Although I had to modify ot because I don't work with a parent-row or
simple
> tree-structure, but with an even relationship (as like every branch could
be[vbcol=seagreen]
> of several tee's...). Can anybody confirm me that this works correct? It
> seems so after some tests...
>
> **************************
> drop table #b
> create table #b (ID integer, level int, lr int)
> declare @.i int
> select @.i = 1
> insert #b select 1, 1, 3
> while @.@.rowcount <> 0
> begin
> select @.i = @.i + 1
> insert #b
> select distinct Cow2ID, @.i, 0
> from tblCowHadIntercourse, #b
> where Cow1ID = #b.id
> and Cow2ID not in (select id from #b where (ID = Cow2ID) and (lr = 0))
> insert #b
> select distinct Cow1ID, @.i, 1
> from tblCowHadIntercourse, #b
> where Cow2ID = #b.id
> and Cow1ID not in (select id from #b where (ID = Cow1ID) and (lr = 1))
> end
> select distinct id
> from #b
> order by id
> *******************************
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:%23yx%23RAKeFHA.2700@.tk2msftngp13.phx.gbl...
i[vbcol=seagreen]
with[vbcol=seagreen]
time,[vbcol=seagreen]
intercourse[vbcol=seagreen]
have[vbcol=seagreen]
cow[vbcol=seagreen]
> cow
had[vbcol=seagreen]
can
>
|||I was working on the sollution, some time passes over,
other solutions come in view, so it's a bit late.
But still here is a solution.
It is a leasy solution but stil complete.
It doesn't depend on existing tables, all tables
get created on the fly. (And cleaned up afterwards).
cowcow contains all the relations.
dcowcow containts all the relations in both directions. (d stands for
double)
cowlevel contains all the 'linked' cows and the number of removed links
I do not know what the purpose of the code is, so I left out the iteration
coding, you have to run the repeat part by 'hand'.
-- START OF DE CODE EXAMPLE
-- ADD MORE RELATIONS WITH THE INSERT COWCOW LINES
-- RUN THE PART BETWEEN THE XX LINES THE REQUIRED NUMBER OF TIMES
-- prepare source table with data
select 1 as cow_id1, 2 as cow_id2 into cowcow
insert cowcow values(1,3)
insert cowcow values(1,2)
insert cowcow values(2,3)
insert cowcow values(1,3)
insert cowcow values(2,8)
insert cowcow values(7,6)
insert cowcow values(2,16)
insert cowcow values(23,24)
insert cowcow values(56,7)
select * from cowcow
-- Make a second table
-- so the source that we can leave the source table as is.
select * into dcowcow from cowcow
-- Duplicate all entries in mirror
-- So that we do not have to concern ourselfs with the who is the first
and/or second cow
insert into dcowcow (cow_id1, cow_id2) select cow_id2, cow_id1 from cowcow
select * from dcowcow
-- Put the first sick cow into the sick table
-- called cowlevel (In this example the sick cow is 1)
select 1 cow_id1, 0 levelx into cowlevel
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXxx
-- Repeat the following code (by hand ?) until the table does not grow.
declare @.level int
select @.level = max(levelx)+1 from cowlevel
print @.level
insert into cowlevel (cow_id1, levelx)
select cow_id1, @.level
from dcowcow D where
exists (select * from cowlevel L where d.cow_id2 = L.cow_id1)
and not exists (select * from cowlevel L2 where d.cow_id1 = L2.cow_id1)
select * from cowlevel order by levelx
-- Repeat finishes here
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXxxx
-- Clean up
drop table cowcow
drop table dcowcow
drop table cowlevel
-- END OF THE CODE EXAMPLE
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:#yx#RAKeFHA.2700@.tk2msftngp13.phx.gbl...
> Hi,
> For my new application I need some really daring and special query, and i
> don't know how to do this the best way. I hope somebody can help me.
> The situation:
> I have 2 tables:
> tblMyCows: table with all my cows in it
> --
> CowID: unique ID of a Cow
> CowName: name of my cow etc
> tblCowHadIntercourse: table that links the Cows that had intercourse with
> each other
> --
> Cow1ID: ID of the first Cow
> Cow2ID: ID of the second Cow
> -> so Cow1ID and Cow2ID had intercourse with each other :-)
> Cows always have intercourse with another cow, and only one Cow at a time,
> but 1 Cow can have intercourse with any other Cow.
> now comes the Problem:
> Suddenly one of my cows has a disease (no wonder of they have intercourse
> like that!). And I need to know the CowID's of all the cows that may have
> this disease also. So I need:
> - all the CowID's of the Cows that had intercourse with the sick Cow = cow
> group 1
> - all the CowID's of the Cows that had intercourse with the Cows my sick
cow
> had intercourse
> with
> - all the CowID's of the Cows that had intercourse with the Cows that had
> intercourse with
> the Cows my sick cow had intercourse with
> - ...
> So basicly it should give me all the potential victims of the disease.
> I hope I explained the problem good enough. And I really hope somebody can
> help me with this, I'm really stuck on it! Note that each cow can be in
> Cow1ID and in Cow2ID for another record...
> Thanks a lot in advance,
> Pieter
>
>
|||Thanks! It seems to have the same principles of one of the other solutions I
find. I guess this is the easiest and best working solution fr my problem.
Thanks a lot!
In fact, I'm not using it for cows, but for a document-system: I need to
have all the documents that have any link woth a certain document etc :-)
Pieter
"ben brugman" <ben@.niethier.nl> wrote in message
news:u7JodCNeFHA.1920@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I was working on the sollution, some time passes over,
> other solutions come in view, so it's a bit late.
> But still here is a solution.
> It is a leasy solution but stil complete.
> It doesn't depend on existing tables, all tables
> get created on the fly. (And cleaned up afterwards).
> cowcow contains all the relations.
> dcowcow containts all the relations in both directions. (d stands for
> double)
> cowlevel contains all the 'linked' cows and the number of removed links
> I do not know what the purpose of the code is, so I left out the iteration
> coding, you have to run the repeat part by 'hand'.
>
> -- START OF DE CODE EXAMPLE
> -- ADD MORE RELATIONS WITH THE INSERT COWCOW LINES
> -- RUN THE PART BETWEEN THE XX LINES THE REQUIRED NUMBER OF TIMES
> --
> -- prepare source table with data
> --
> select 1 as cow_id1, 2 as cow_id2 into cowcow
> insert cowcow values(1,3)
> insert cowcow values(1,2)
> insert cowcow values(2,3)
> insert cowcow values(1,3)
> insert cowcow values(2,8)
> insert cowcow values(7,6)
> insert cowcow values(2,16)
> insert cowcow values(23,24)
> insert cowcow values(56,7)
> select * from cowcow
>
> -- Make a second table
> -- so the source that we can leave the source table as is.
> select * into dcowcow from cowcow
> -- Duplicate all entries in mirror
> -- So that we do not have to concern ourselfs with the who is the first
> and/or second cow
> insert into dcowcow (cow_id1, cow_id2) select cow_id2, cow_id1 from cowcow
> select * from dcowcow
> -- Put the first sick cow into the sick table
> -- called cowlevel (In this example the sick cow is 1)
> select 1 cow_id1, 0 levelx into cowlevel
> -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXxx
> -- Repeat the following code (by hand ?) until the table does not grow.
> --
> declare @.level int
> select @.level = max(levelx)+1 from cowlevel
> print @.level
> insert into cowlevel (cow_id1, levelx)
> select cow_id1, @.level
> from dcowcow D where
> exists (select * from cowlevel L where d.cow_id2 = L.cow_id1)
> and not exists (select * from cowlevel L2 where d.cow_id1 = L2.cow_id1)
> select * from cowlevel order by levelx
> --
> -- Repeat finishes here
> -- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXxxx
>
> --
> -- Clean up
> --
> drop table cowcow
> drop table dcowcow
> drop table cowlevel
> -- END OF THE CODE EXAMPLE
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:#yx#RAKeFHA.2700@.tk2msftngp13.phx.gbl...
i[vbcol=seagreen]
with[vbcol=seagreen]
time,[vbcol=seagreen]
intercourse[vbcol=seagreen]
have[vbcol=seagreen]
cow[vbcol=seagreen]
> cow
had[vbcol=seagreen]
can
>

Saturday, February 25, 2012

Really interesting issue!

Really interesting issue!

Really interesting issue!

Really interesting issue!

Really interesting behaviour of ADO

Really interesting behaviour of ADO

Really important, i need your help!

Really easy/dumb SQL question...

Really easy stored proc question

Really dumb Stored Proc Question. But I need help and Im lost.

Really Daring SQL-Query: with Cows having fun!

Real Time DSS

Blog Archive

About Me