|
Anybody who worked on sql server reporting, can you please tell me how to improve performance of any report.
Thanks & Regards
Mishra
|
|
|
|
|
Your question is a little too broad for anyone to be able to answer. The performance of your reports is directly coupled to the performance of the queries used to supply the data for your reports. So the improve the performance of any report you could tune your query.
If you've already tuned your query look into the administration options which allow you to schedule the report to run every so often and cache the output, for example you could have it run every night. As long as you don't need real-time data, which most reports don't absolutely need, then your users will not have to wait so long for the report and it won't bring down your database when you have multiple users running the same report.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
I m using this SP in SQL 2005 and Getting this error plz help me
Warning: Null value is eliminated by an aggregate or other SET operation
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[systek_salesRpt]
@username varchar(50) = NULL,
@RoleID int = NULL,
@ItemTitle varchar(50) = NULL,
@jumpid int
AS
BEGIN
CREATE TABLE #saletemp
(
SubscriptionID int,
Noofsales int,
subscriberID int ,
OwnerID int,
Rolename varchar(50),
Type varchar(50),
Itemid int,
Totalprice float,
Commission float,
RoleID int,
ItemTitle varchar(50),
CreatedDate DateTime,
Username varchar(50)
)
INSERT INTO #saletemp(SubscriptionID,Noofsales,subscriberID,OwnerID,Rolename,Type,Itemid,Totalprice,Commission,RoleID,ItemTitle,CreatedDate,UserName)
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(D.VideoID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
D.VideoTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'videos')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='videos')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
INNER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,B.CreatedDate,H.UserName
UNION ALL
------------------------------------------------------------------series-------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(E.SeriesId,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
E.seriesTitle as ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'series')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='series')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,A.RoleID,E.seriesTitle,B.CreatedDate,H.userName
UNION ALL
-----------------------------------------------------------group id----------------------------------------------------------------------------
SELECT B.SubscriptionID,
COUNT(B.SubscriptionID) AS noofsales,
A.SubscriberID,A.UserID AS OwnerID,
C.RoleName,B.Type,
ISNULL(F.GroupID,'') AS ItemID,
ISNULL(SUM(B.price),0) AS TotalPrice,
ISNULL(SUM(A.Commission),0) AS Commission,A.RoleID,
F.GroupName AS ItemTitle,B.CreatedDate,H.userName
FROM
(SELECT * FROM Systek_UserCommissions WHERE Systek_UserCommissions.Module = 'Groups')A
LEFT OUTER JOIN
(SELECT * FROM Systek_Subscription WHERE Type='Groups')B
ON A.SubscriberID = B.UserID
LEFT OUTER JOIN
(SELECT * FROM SIC_Roles )C
ON C.RoleID = A.RoleID
LEFT OUTER JOIN
(SELECT * FROM systek_videos)D
ON D.VideoID = B.ProductID OR D.VideoID = A.itemid
LEFT OUTER JOIN
(SELECT * FROM systek_Series)E
ON E.SeriesId = D.SeriesId OR E.SeriesId = B.ProductID OR E.SeriesId = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM SIC_SmartThinker_Group)F
ON F.GroupID = A.ItemID
LEFT OUTER JOIN
(SELECT * FROM Systek_VideoProducer)G
ON A.UserID = G.userID
LEFT OUTER JOIN
(SELECT * FROM sic_users)H
ON H.UserID = G.UserID OR B.UserID = H.UserID
GROUP BY
B.SubscriptionID,A.SubscriberID,A.UserID,C.RoleName,B.Type,D.VideoTitle,
E.SeriesId,F.GroupID,D.VideoID,C.RoleID,A.RoleID,F.GroupName,B.CreatedDate,H.username
----------------------------------------------------removing duplicate subscrptionid---------------------------------------------------------
DECLARE @Count int
DECLARE @SubsCriptionID AS int
DECLARE vendor_cursor CURSOR
FOR
SELECT SubscriptionID,Count(SubscriptionID) - 1 FROM #saletemp Where RoleName <> 'smartIcast' GROUP BY SubscriptionID HAVING Count(subscriptionid) > 1
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @Count
DELETE FROM #saletemp WHERE #saletemp.SubscriptionID = @SubsCriptionID
SET ROWCOUNT 0
FETCH NEXT FROM vendor_cursor INTO @SubsCriptionID,@Count
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor
----------------------------------------------------------------------------------------------------------------------------------------------
IF @jumpid = 0
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Rolename,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
END
IF @jumpid = 1
BEGIN
SELECT subscriptionid,noofsales,subscriberid,ownerid,Rolename,Type,itemID,
'$' + (CAST(ROUND(TotalPrice,2) AS Varchar)) AS TotalPrice,
'$' + (CAST(ROUND(Commission,2) AS Varchar)) AS Commission,
RoleID,ItemTitle,CreatedDate,UserName
FROM #saletemp
Where
RoleID = @RoleID
OR
ItemTitle LIKE '%'+ @ItemTitle + '%'
OR
username LIKE '%' + @username + '%'
END
DROP TABLE #saletemp
END
|
|
|
|
|
hyderalishah wrote: Warning: Null value is eliminated by an aggregate or other SET operation
This is a warning, notan error. It is because you are doing calculations on columns which can be null. It is warning you that those values (i.e. the null) will not be included. For sum(), count() and so on this is nt usually important, but it will be gor avg etc.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I created a stored procedure in MySQL which returned one row in the result set. Since there is only one row in the table, this it what was supposed to happen. I had to add anouther column to the table, which I did without any problem. Then I modified the SELECT...FROM...WHERE query to include the column, no errors so it should be working correctly. The problem is that now it is returning 25 rows in the result set, all containing the same information. If I add another row to the table, it returns 50 rows - 25 copies of each row in the table.
How is that possible?
INTP
"Program testing can be used to show the presence of bugs, but never to show their absence."Edsger Dijkstra
|
|
|
|
|
Can we see your query?
I Love T-SQL
|
|
|
|
|
|
WHERE locations.id = _locationID
This _locationID is parameter? If it is not parameter then must be table and u must specify column like :
WHERE locations.id = _locationID.Column
Also can u add in WHERE clause column destiny with any condition?
I Love T-SQL
|
|
|
|
|
Sorry I had to delete that mess. The 5 WHERE id compares and 5 SELECT IF() were the cause of the 25 returns - 5 x 5. The solution I came up with was to LEFT JOIN the series of required rows and remove the comparisons from the WHERE.
FROM locations,
loc_staticdata,
loc_staticdata_waters AS sw
LEFT JOIN textures AS d1 ON sw.surfaceTextureID = d1.id
LEFT JOIN textures AS d2 ON sw.shoreTextureID = d2.id
LEFT JOIN textures AS d3 ON sw.envMapOverTextureID = d3.id
LEFT JOIN textures AS d4 ON sw.envMapUnderTextureID = d4.id
LEFT JOIN textures AS d5 ON sw.specularMapTextureID = d5.id
WHERE locations.id = _locationID
AND locations.staticDataID = loc_staticdata.id
AND sw.staticDataID = loc_staticdata.id;
I am not sure if it is the best solution, but it works.
Thanks for the attempt. 
INTP
"Program testing can be used to show the presence of bugs, but never to show their absence."Edsger Dijkstra
|
|
|
|
|
I am glad that u found solution, the most important is to work your query, the performance gonna be second with which we have to lookup...
Happy query-ing... 
I Love T-SQL
|
|
|
|
|
hi!
I am having problem with my logic. i have set of records like below
id name num
100 A 1
100 A 2
100 A 3
100 A 4
100 A 46
100 A 47
200 A 3
200 A 4
200 A 5
300 A 1
the data is already ordered by id, name and num. I need to find min(num) and max(num) and group the records for serialised num's for example above record set should be transformed to 4 records as
id name num_min num_max
100 A 1 4
100 A 46 47
200 A 3 5
300 A 1 1
I am having really difficult time as in how my while/if else should work.
I am trying to solve using temp tables/while loop/if else statement. I am not allowed to use cursors. could someone help me how should i do it.
I put all the data in a temp table #tempsrc
set rowcount 1
get all 4 values in a variable
@id, @name, @num
delete the record
if @num+1<> select num from #tempsrc
begin
delete record
end
else
begin
end
while....
if
here is where my problems are could someone point me to right direction
many thanks in forward.
|
|
|
|
|
It doesn't matter what order they are in as long as you are not worried about holes in the order. This is a simple GROUP BY query.
SELECT
id,
name,
MIN(num) AS firstNum,
MAX(num) AS lastNum
FROM
MyTable
GROUP BY
id,
name
|
|
|
|
|
holes in the order is what i am worried about. if its
100 A 1
100 A 2
100 A 3
100 A 4
100 A 46
100 A 47
as mentioned the min and max shouldn't be 1 and 47
it should produce two record records with min max 1,4 and 46,47 respectively.
i thought it was clear what i wanted from my earlier post.
thanks
|
|
|
|
|
if your data looks like this?
100 A 1<br />
100 A 2<br />
100 A 3<br />
100 A 4<br />
100 A 45<br />
100 A 46<br />
100 A 47
then your result have to be like this?
100 A 1<br />
100 A 4<br />
100 A 45<br />
100 A 47
I Love T-SQL
|
|
|
|
|
no i have to have seperate column so its
100 A 1 4
100 A 45 47
for
100 A 1
100 A 4
100 A 45
100 A 47
|
|
|
|
|
If you are worried about the holes then just use correlated subqueries to find the ranges. A WHERE clause would be a pain since you would end up with a tremendous set of loopped queries if the data was of any real size. See if you can grasp this nasty beast:
SELECT
ID,
Name,
num AS StartNumber,
(SELECT
min(num)
FROM
(SELECT
ID,
name,
num
FROM
TestTable b
WHERE
NOT EXISTS (SELECT
*
FROM
TestTable
WHERE
ID = a.ID AND
Name = a.Name AND
num = b.num+1)) tmp
WHERE
num >= a.num) AS EndNumber
FROM
TestTable a
WHERE
NOT EXISTS (SELECT
*
FROM
TestTable
WHERE
ID = a.ID AND
Name = a.Name AND
num = a.num-1)
I tested it with your data set so it works with SQL 2000. Be sure to change the table name (TestTable) and the column names if your database is set up as case sensitive.
The concept comes from finding the start numbers:
SELECT
ID,
name,
num
FROM
TestTable a
WHERE
NOT EXISTS(SELECT * FROM TestTable WHERE
ID = a.ID AND Name = a.Name AND
num = a.num-1)
and the last numbers:
SELECT
ID,
name,
num
FROM
TestTable b
WHERE
NOT EXISTS(SELECT * FROM TestTable WHERE
ID = b.ID AND Name = b.Name AND
num = b.num+1)
You just have to find a way to put the two queries together in a neat correlated package.
|
|
|
|
|
thank you so much for your response. i tried to use this concept in my query at the end i do a group by id,name and num but it takes really long time to do an insert to a table. could you please tell me why its taking such a long time than normal group by query.
thanks
|
|
|
|
|
If you are running this on a large table, you will force a table scan for each portion of the query. Highly inefficient! If this query is run each time you insert a record, you will need to support it with an index.
CREATE INDEX MyIndexName
ON MyTableName
(
id,
name,
num
)
This will slightly slow down a direct INSERT/UPDATE/DELETE but, it should greatly speed up the query to find the holes. This will net you a significant gain if you are running the query on every INSERT.
|
|
|
|
|
Hi, thanks for looking
Im developing a website
On a page i have a listing of products
Products are returned by a really anidated query:
1. I have a view that returns products (Sorted by name)
2. A function then takes this results and makes some calculations and returns a table again (This function is called by many stored procedures and this is the cause of anidated query, not that i like to anidate)
3. The table is then filtered with where clauses by a stored procedure wich is the one that return results to a sqldatasource
My db has a collation of Traditional_Spanish_CI_AS
I added asp.net membership wich has a collation of Modern_Spanish_CI_AS
When i generate the scripts and try to execute them on the remote server, i get an error that says collation conflict, So i tried removing all collations and i executed the scripts succesfully
But now, i have that sorting problem
What could be the cause of this?
Thanks in advance
Alexei Rodriguez
modified on Thursday, April 17, 2008 8:37 PM
|
|
|
|
|
hi paul...
thank u for ur response .but i found answer for every formate....
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
alter FUNCTION [dbo].[GetPhoto](@Photo nvarchar(max),@UploadedDate DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @GetDate DATETIME
DECLARE @strDate varchar(24)
SET @GetDate = @UploadedDate
if NOT (@Photo IS NULL)
BEGIN
--RETURN @GetDate
DECLARE @Startposition int
SET @Startposition= patindex('%Date:%', IsNull(@Photo, '') )+5
SET @strDate = SUBSTRING(@Photo,@Startposition,len(@Photo)-@Startposition)
if (len(@strDate ) < 24)
BEGIN
RETURN @GetDate
END
if not (@strDate IS NULL)
BEGIN
SET @strDate = LEFT(@strDate ,patindex('%|%',@strPhotoDate )-1)
SET @strDate = replace(@strDate ,'th','')
SET @strDate = replace(@strDate ,'nd','')
SET @strDate = replace(@strDate ,'rd','')
SET @strDate = replace(@strDate ,'st','')
SET @strDate = replace(@strDate ,',','')
set @GetDate = cast(@strDate as DATETIME)
-- set @GetPhotoDate = convert(char,Convert(DateTime,@strPhotoDate,101))
-- set @GetPhotoDate = cast(CONVERT(datetime,@strPhotoDate)-convert(datetime,substring(@strPhotoDate,3,3)) as datetime)
--cast(@GetPhotoDate as DATETIME)
END
END
RETURN @GetPhotoDate
END
like that...
it will convert the 4/12/2008,4-july-2007....but not converting the 4th july 2008
22nd june 2008.
thanks in advance
Thanks
Subbu.
|
|
|
|
|
select convert(varchar(20),convert(datetime,'3 august 1997'),103) will give you this result 03/08/1997
hope it will help...
I Love T-SQL
modified on Thursday, April 17, 2008 10:12 PM
|
|
|
|
|
Hi ,
Thanks for ur response....
select convert(varchar(20),convert(datetime,'3 august 1997'),103)querry works fine .
but i need to convert the date...'3th august 1997' .
if u find any solution then pls....... send me
Thanks in advance
Thanks
Subbu.
|
|
|
|
|
I have an insert trigger on a table that checks a certain set of conditions, and in certain cases, i want it to raise an error and stop the insert from happening.
I've used RAISEERROR to, not surprisingly, raise my error, but if i don't specify a severity of 20 or greater, then the insert just goes ahead anyway and the error gets reported back. I can't specify 20 or greater, as the user that creates the trigger then needs to be a system admin and that's not an option for me.
Has anyone any better idea of how to do this?
|
|
|
|
|
Did you add a 'Return' after the error has been raised?
Example:
IF x<br />
BEGIN<br />
RAISERROR x <br />
RETURN<br />
END
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
If it's SQL Server, use the instead of trigger.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
