USE [MainTable]
GO
/****** Object: StoredProcedure [dbo].[ManagerSendReceiveDetail] Script Date: 03/02/2012 21:31:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ManagerSendReceiveDetail]
(
@FirstName nvarchar(500),
@LastName NVARCHAR(500),
@FromDate nvarchar(500),
@ToDate nvarchar(500),
@Diff INT,
@Date NVARCHAR(100),
@GetDay NVARCHAR(100)
)
AS
BEGIN
CREATE TABLE ManagerSendReceiveDetailTable
(
logTime NVARCHAR(500),
Sent NVARCHAR(500),
Received NVARCHAR(500)
)
Create Index INDXlogTime
ON ManagerSendReceiveDetailTable(logTime)
DECLARE @UserID NVARCHAR(10)
DECLARE @UnitID NVARCHAR(10)
SELECT @UserID=UserID FROM
dbo.Users
WHERE firstname=@FirstName AND LastName=@LastName
DECLARE @SQl NVARCHAR(max)
WHILE @diff>=0
BEGIN
SET @Sql='INSERT INTO ManagerSendReceiveDetailTable SELECT
LEFT( CAST(Dateadd(day, Datediff(day, 0,logTime), 0)AS NVARCHAR),11) AS ''logTime''
,
sum(wpl.bytesrecvd) as ''Sent'',
sum(WPL.bytessent) as ''Received''
FROM ISALOG_'+ @Date+@getday+ '_WEB_000.dbo.WebProxyLog WPL
INNER JOIN Users U
ON U.ClientIP=WPL.ClientIP
WHERE logTime>='''+ @FromDate+ ''' and
logTime<='''+ @ToDate+ '''AND u.UserID='''+ @UserID+ '''
group by Dateadd(day, Datediff(day, 0, logTime), 0)
ORDER BY 1'
SET @diff=@diff-1
SET @getday=@getday+1
IF LEN(@getday)=1
Begin
SET @Getday='0'+CAST(@getday AS nvarchar(10))
END
EXEC (@sql)
END
SELECT * FROM ManagerSendReceiveDetailTable
DROP TABLE ManagerSendReceiveDetailTable
END
-----
tnx
USE [MainTable]
GO
/****** Object: StoredProcedure [dbo].[ManagerSendReceiveDetail] Script Date: 03/02/2012 21:31:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ManagerSendReceiveDetail]
(
@FirstName nvarchar(500),
@LastName NVARCHAR(500),
@FromDate nvarchar(500),
@ToDate nvarchar(500),
@Diff INT,
@Date NVARCHAR(100),
@GetDay NVARCHAR(100)
)
AS
BEGIN
CREATE TABLE ManagerSendReceiveDetailTable
(
logTime NVARCHAR(500),
Sent NVARCHAR(500),
Received NVARCHAR(500)
)
Create Index INDXlogTime
ON ManagerSendReceiveDetailTable(logTime)
DECLARE @UserID NVARCHAR(10)
DECLARE @UnitID NVARCHAR(10)
SELECT @UserID=UserID FROM
dbo.Users
WHERE firstname=@FirstName AND LastName=@LastName
DECLARE @SQl NVARCHAR(max)
WHILE @diff>=0
BEGIN
SET @Sql='INSERT INTO ManagerSendReceiveDetailTable SELECT
LEFT( CAST(Dateadd(day, Datediff(day, 0,logTime), 0)AS NVARCHAR),11) AS ''logTime''
,
sum(wpl.bytesrecvd) as ''Sent'',
sum(WPL.bytessent) as ''Received''
FROM ISALOG_'+ @Date+@getday+ '_WEB_000.dbo.WebProxyLog WPL
INNER JOIN Users U
ON U.ClientIP=WPL.ClientIP
WHERE logTime>='''+ @FromDate+ ''' and
logTime<='''+ @ToDate+ '''AND u.UserID='''+ @UserID+ '''
group by Dateadd(day, Datediff(day, 0, logTime), 0)
ORDER BY 1'
SET @diff=@diff-1
SET @getday=@getday+1
IF LEN(@getday)=1
Begin
SET @Getday='0'+CAST(@getday AS nvarchar(10))
END
EXEC (@sql)
END
SELECT * FROM ManagerSendReceiveDetailTable
DROP TABLE ManagerSendReceiveDetailTable
END
-----
tnx
A few things jump out at me. First, is that if a positive number is passed to the @Diff parameter, you will have an infinite loop. Those tend to be bad.
Next, this part, "FROM ISALOG_'+ @Date+@getday+ '_WEB_000.dbo.WebProxyLog WPL " makes it appear that you have several WebProxyLog tables. If that's true, maintenance is a lot more difficult than it needs to be.
Finally, since you are essentially selecting data that you already have, inserting into a temp table seems to be unnecessary.
In your OP, you said this SP returns 32,000 rows? What does your application do with them?
(that if a positive number is passed to the @Diff parameter):
yes it's possitive ,as you said there are many tables in my SP.
I'll have new DB every day,like this:
ISALOG_20110801_WEB_000.mdb
ISALOG_20110802_WEB_000.mdb
..
..
..
ISALOG_20110830_WEB_000.mdb
imagine that User wants to select 3 days.
I used Diff to find out which databases should be retrieve.if you look at databases name they have similar name :
ISALO_
_WEB_000.mdb
but dates are diffrent
20110801
20110802
....
20110830
so user select 3 days ,for instance 2011/08/01 to 2011/08/08
diff=2011/08/08 - 2011/08/01 (equal 8)
-----
In your OP, you said this SP returns 32,000 rows?
well it would be more than 320,000 rows ,each table will have 320,000 or more.
imagine that 8 tables will have Avg=320,000*8
------
What does your application do with them?
in my Co ,they have TMG which stores users infortaion,something like they visited sites,logtime,Url,clientIp,browser and so on.
i want to report my users every day to find out which site they have visited,which browser they have used and so many availabe reports.
----
and thank you so much about responding me.
I would have gone for a different design. I would not have any access databases. Given your earlier post with the SP code, I assume you have sql server. I would have a few tables in a single sql server db. I would have a datetime field in the necessary
tables instead of a different table for every day.
To get stats for reports, I would take advantage of sql functions such as count(), min(), max(), and avg().
But that's just me.
Marked as answer by vahid.ch on Mar 03, 2012 05:12 AM
vahid.ch
Member
294 Points
331 Posts
Select billion rows fastest
Feb 28, 2012 07:24 AM|LINK
Hi there,I have a database which has billion rows in it.
when I want to select them it takes long ,long,long time,for instance for 320000 rows it takes 17 secs.
imagine that I want to select-where statement in it,how can I select it faster?
I use SP,Index to have better performance but it takes long time.
yrb.yogi
Star
14460 Points
2402 Posts
Re: Select billion rows fastest
Feb 28, 2012 07:38 AM|LINK
Have you indexed the where clause column?
17 secs is too long time to select only 320000 records.
Try to cover where cluase columns to index either cluster or non-cluster..
Have you checked your execution plan? check first.
to know about execution plan..
http://msdn.microsoft.com/en-us/library/ms178071.aspx
.Net All About
Chen Yu - MS...
All-Star
21584 Points
2493 Posts
Microsoft
Re: Select billion rows fastest
Mar 02, 2012 08:41 AM|LINK
Hi vahid.ch,
Please post your stored procedure, it will provide us more information about your problem.
Thanks.
Feedback to us
Develop and promote your apps in Windows Store
nilsan
All-Star
16892 Points
3707 Posts
Re: Select billion rows fastest
Mar 02, 2012 01:05 PM|LINK
If you are planning to show all the data on front end then that's worst idea. If not, please provide more info, so that people can help you out.
Blog | Get your forum question answered | Microsoft Community Contributor 2011
vahid.ch
Member
294 Points
331 Posts
Re: Select billion rows fastest
Mar 02, 2012 05:02 PM|LINK
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Select billion rows fastest
Mar 02, 2012 05:17 PM|LINK
A few things jump out at me. First, is that if a positive number is passed to the @Diff parameter, you will have an infinite loop. Those tend to be bad.
Next, this part, "FROM ISALOG_'+ @Date+@getday+ '_WEB_000.dbo.WebProxyLog WPL " makes it appear that you have several WebProxyLog tables. If that's true, maintenance is a lot more difficult than it needs to be.
Finally, since you are essentially selecting data that you already have, inserting into a temp table seems to be unnecessary.
In your OP, you said this SP returns 32,000 rows? What does your application do with them?
vahid.ch
Member
294 Points
331 Posts
Re: Select billion rows fastest
Mar 02, 2012 05:37 PM|LINK
(that if a positive number is passed to the @Diff parameter):
yes it's possitive ,as you said there are many tables in my SP.
I'll have new DB every day,like this:
ISALOG_20110801_WEB_000.mdb
ISALOG_20110802_WEB_000.mdb
..
..
..
ISALOG_20110830_WEB_000.mdb
imagine that User wants to select 3 days.
I used Diff to find out which databases should be retrieve.if you look at databases name they have similar name :
ISALO_
_WEB_000.mdb
but dates are diffrent
20110801
20110802
....
20110830
so user select 3 days ,for instance 2011/08/01 to 2011/08/08
diff=2011/08/08 - 2011/08/01 (equal 8)
-----
In your OP, you said this SP returns 32,000 rows?
well it would be more than 320,000 rows ,each table will have 320,000 or more.
imagine that 8 tables will have Avg=320,000*8
------
What does your application do with them?
in my Co ,they have TMG which stores users infortaion,something like they visited sites,logtime,Url,clientIp,browser and so on.
i want to report my users every day to find out which site they have visited,which browser they have used and so many availabe reports.
----
and thank you so much about responding me.
nilsan
All-Star
16892 Points
3707 Posts
Re: Select billion rows fastest
Mar 02, 2012 05:44 PM|LINK
Could you apply SET BASED APPROACH rather than looping?
Blog | Get your forum question answered | Microsoft Community Contributor 2011
vahid.ch
Member
294 Points
331 Posts
Re: Select billion rows fastest
Mar 02, 2012 05:54 PM|LINK
how can I make my tables permanent?
as I said i'll have new DB everyday.
if i'm not wrong you say that I merge them together,yes?
------------------
Can you provide little more info on what values being passed and what's being done?
as I said in last post users surf many webistes during a day,from they working time.
each opened website will be stored in database.
day 1
day 2 and so on.
because of some roles ,users don't have permission to surf every disired websites,that's why a manager will look up users logs and...
------------
i'm completely sure that my used SP is not reliable thing,any suggestion is welcome.
tnx
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Select billion rows fastest
Mar 02, 2012 06:00 PM|LINK
I would have gone for a different design. I would not have any access databases. Given your earlier post with the SP code, I assume you have sql server. I would have a few tables in a single sql server db. I would have a datetime field in the necessary tables instead of a different table for every day.
To get stats for reports, I would take advantage of sql functions such as count(), min(), max(), and avg().
But that's just me.