March 14, 2012 at 12:01 am
Hi there,I have a table which consists 5,000,000 rows.
and also I join my table to another table and use this query:
SELECT SUM(wpl.bytesrecvd)as 'SENT'
,SUM(WPL.bytessent) as 'Received'
,U.FirstName,u.LastName,Un.UnitName FROM WebProxyLog WPL
INNER JOIN MainTable.dbo.Users U
ON U.ClientIP=WPL.ClientIP
INNER JOIN MainTable.dbo.Units UN
ON UN.UnitID=u.UnitID
WHERE logTime>=''+ @FromDate+ '' and
logTime<=''+ @ToDate+ '' AND u.UnitID=''+@unitID+'' AND u.ClientIP!=''+@ClientIP+''
group by U.FirstName , U.LastName,Un.UnitName
Indexes:
bytesrecvd
bytessent
ClientIP
logTime
UnitID
Executing this query for these values take 2 mins:
@fromdate:2011-11-22
@toDate:2011-11-24
@clientIP:something
@UnitID:something
March 14, 2012 at 1:05 am
Table definitions, index details (all of the details) and actual execution plan would all be a gret help, but my initial thoughts (guesses) are:
a) Index on logtime column - is it on the one column, or are all those columns you lis a single composite index.?
b) What is the reason behind all the string concatenation of zero-length strings with the date params?
logTime>=''+ @FromDate+ '' etc.
I cannot see why you need the ''+ +'' and because of all the implied data type conversions may result in scans even if indexes are on logtime column.
c) data types of logtime and @fromdate and @todate are date/datetime or similar I hope?
Other useful info would include - how many rows from the 5 million are being returned, and what statistics do you have on these tables?
Mike
March 14, 2012 at 1:10 am
Did try like this?
SELECT SUM(wpl.bytesrecvd)as 'SENT'
,SUM(WPL.bytessent) as 'Received'
,U.FirstName,u.LastName,Un.UnitName
FROM
MainTable.dbo.Units UN
INNER JOIN MainTable.dbo.Users U ON UN.UnitID=u.UnitID
INNER JOIN WebProxyLog WPL ON U.ClientIP=WPL.ClientIP
WHERE
u.UnitID=@unitID AND u.ClientIP!=@ClientIP
AND logTime>= @FromDate and logTime<= @ToDate
group by U.FirstName , U.LastName,Un.UnitName
March 14, 2012 at 1:51 am
tnx for ur responding
following your questions:
a)is it on the one column:Exactly
b)wrong query,I amended it.
c)yes,it's Datetime
how many rows from the 5 million are being returned?
by executing above SP,it returns maximum 12 rows.
I attached details as follows:
March 14, 2012 at 2:22 am
Create an index on WebProxyLog. key columns: logTime, ClientIP. Include columns bytesrecvd, bytessent
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 14, 2012 at 2:25 am
oh thank you very much,really helpful.
March 14, 2012 at 2:40 am
Let me amend that.
Create a clustered index on the WebProxyLog table, key column = logdate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply