How to get this done with one connection? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get this done with one connection?

Author

Topic

kfj_de
Starting Member

12 Posts

Posted - 2007-07-15 : 18:51:35

I am normally doing most of the programming in ASP.NET and use database functions only at a very basic lecel. But now we discovered some performance problems because of too much connections in ASP.NET to the database. Perhaps the following might be trivial to you, but is not at the moment for us:

We are writing in a log and want to extract the statistics from the log like the following

SELECT COUNT(*)
FROM Statistik
WHERE (DateTime > getdate()-@days)

We would like to have that with ONE database connection with all of the following values for @days: 1 (2),1-7 (8-14),1-30(31-60) and use it in ASP.NET.

So the results should look like that:

Last 24 hours: XXX ( Compared to the 24 hous before: XXX)
Last 7 days: XXX ( Compared to the 7 days before: XXX)
Last 30 days: XXX ( Compared to the last 30 days before: XXX)

So far I do not have an idea, which way would be the right way. Any help weill be appreciated.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-15 : 20:37:10

You only need one connection string if data are from single db, and you can run multiple queries once connect to sql server.

kfj_de
Starting Member

12 Posts

Posted - 2007-07-16 : 02:14:02

Since all of the 6 queries are now in the page load of a single ASP.NET page and the database holds about 200k records and is getting bigger this slows down the system a lot.

My idea was to put all that in one query or stored procedure to minimize the traffic between ASP.NET and the database.

How can I get the 6 results above from only ONE query or stored procedure?

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-07-16 : 02:59:36

My suggestion would be that you read up about Indexes as it sounds as though one may improve this query. I also suggest you look at the DATETIME sections in here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210[/url] to make sure you are extracting what you think you are.

Then put it into a stored procedure.

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

kfj_de
Starting Member

12 Posts

Posted - 2007-07-16 : 03:12:42

This is exactly, what I think I need.

These are the complete SELECT-Statements:

SELECT COUNT(*) AS PIsHours24
FROM Statistik
WHERE (DateTime > getdate()-1)

SELECT COUNT(*) AS VisitsHours24
FROM Statistik
WHERE (DateTime > getdate()-1) AND NewSession=1

SELECT COUNT(*) AS PIsHours25to48
FROM Statistik
WHERE (DateTime < getdate()-1) AND (DateTime >getdate()-2)

SELECT COUNT(*) AS Visits25to48
FROM Statistik
WHERE (DateTime < getdate()-1) AND (DateTime >getdate()-2) AND NewSession=1

SELECT COUNT(*) AS PIsLast7Days
FROM Statistik
WHERE (DateTime > getdate()-7)

SELECT COUNT(*) AS VisitsLast7Days
FROM Statistik
WHERE (DateTime > getdate()-7)AND NewSession=1

SELECT COUNT(*) AS PIsDays8to14
FROM Statistik
WHERE (DateTime <getdate()-7) AND (DateTime >getdate()-14)

SELECT COUNT(*) AS VisitsDays8to14
FROM Statistik
WHERE (DateTime <getdate()-7) AND (DateTime >getdate()-14) AND NewSession=1

SELECT COUNT(*) AS PIsLast30Days
FROM Statistik
WHERE (DateTime >getdate()-30)

SELECT COUNT(*) AS VisitsLast30Days
FROM Statistik
WHERE (DateTime >getdate()-30) AND NewSession=1

SELECT COUNT(*) AS PIsDays31to60
FROM Statistik
WHERE (DateTime < getdate()-30) AND (DateTime >getdate()-60)

SELECT COUNT(*) AS PIsDays31to60
FROM Statistik
WHERE (DateTime < getdate()-30) AND (DateTime >getdate()-60) AND NewSession=1

They work perfect for themselves. But how do I tie them together with one stored procedure? I do not know how to write this, because I did'nt do something before with multiple results. Everything I tried so far didn't work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 03:26:16

[code]SELECT SUM(CASE
WHEN DateTime >= DATEADD(DAY, -1, CURRENT_TIMESTAMP) THEN 1
ELSE 0
END) AS PIsHours24,
SUM(CASE
WHEN DateTime >= DATEADD(DAY, -1, CURRENT_TIMESTAMP) AND NewSession = 1 THEN 1
ELSE 0
END) AS VisitsHours24,
SUM(CASE
WHEN DateTime BETWEEN DATEADD(DAY, -2, CURRENT_TIMESTAMP) AND DATEADD(DAY, -2, CURRENT_TIMESTAMP) THEN 1
ELSE 0
END) AS PIsHours25to48,
SUM(CASE
WHEN DateTime BETWEEN DATEADD(DAY, -2, CURRENT_TIMESTAMP) AND DATEADD(DAY, -2, CURRENT_TIMESTAMP) AND NewSession = 1 THEN 1
ELSE 0
END) AS VisitsHours25to48,
SUM(CASE
WHEN DateTime >= DATEADD(DAY, -7, CURRENT_TIMESTAMP) THEN 1
ELSE 0
END) AS PIsLast7Days,
SUM(CASE
WHEN DateTime >= DATEADD(DAY, -7, CURRENT_TIMESTAMP) AND NewSession = 1 THEN 1
ELSE 0
END) AS VisitsLast7Days,
SUM(CASE
WHEN DateTime BETWEEN DATEADD(DAY, -14, CURRENT_TIMESTAMP) AND DATEADD(DAY, -7, CURRENT_TIMESTAMP) THEN 1
ELSE 0
END) AS PIsDays8to14,
SUM(CASE
WHEN DateTime BETWEEN DATEADD(DAY, -14, CURRENT_TIMESTAMP) AND DATEADD(DAY, -7, CURRENT_TIMESTAMP) AND NewSession = 1 THEN 1
ELSE 0
END) AS VisitsDays8to14,
SUM(CASE
WHEN DateTime >= DATEADD(DAY, -30, CURRENT_TIMESTAMP) THEN 1
ELSE 0
END) AS PIsLast30Days,
SUM(CASE
WHEN DateTime >= DATEADD(DAY, -30, CURRENT_TIMESTAMP) AND NewSession = 1 THEN 1
ELSE 0
END) AS VisitsLast30Days,
SUM(CASE
WHEN DateTime BETWEEN DATEADD(DAY, -60, CURRENT_TIMESTAMP) AND DATEADD(DAY, -30, CURRENT_TIMESTAMP) THEN 1
ELSE 0
END) AS PIsDays31to60,
SUM(CASE
WHEN DateTime BETWEEN DATEADD(DAY, -60, CURRENT_TIMESTAMP) AND DATEADD(DAY, -30, CURRENT_TIMESTAMP) AND NewSession = 1 THEN 1
ELSE 0
END) AS VisitsDays31to60
FROM Statistik
WHERE DateTime >= DATEADD(DAY, -60, CURRENT_TIMESTAMP)[/code]

Peter Larsson
Helsingborg, Sweden

kfj_de
Starting Member

12 Posts

Posted - 2007-07-16 : 03:55:06

Thx a lot for your quick and really good reply...This was exactly what I was looking for.

One more question, if I take a deeper look into the code you rote:

What differences are between getdate()-1 and DATEADD(DAY, -1, CURRENT_TIMESTAMP)? Did you use it, because of the context or because of performance issues or because of something else?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 04:00:19

To make sure there is no conversion between datatypes.
And easier maintenance if some other consultant/developer is invited to the project.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources