August 2, 2005 at 1:18 am
i need to retrieve a resultant set which includes only those columns which are entirely non zero.. the foll ex should explain what i need
UserId TradeId Trade Start Value End Value
001 101 Citibank 10.0 0.0
001 102 Citibank 10.0 0.0
001 103 HDFC 11.0 0.0
001 104 Citibank 10.0 0.0
002 105 Citibank 10.0 09.0
003 106 Citibank 10.0 10.0
002 107 Citibank 10.0 0.0
now if i query for userid '001' it should return only those cols for which all values are not 0.0.. it should look like
UserId TradeId Trade Start Value
001 101 Citibank 10.0
001 102 Citibank 10.0
001 103 HDFC 11.0
001 104 Citibank 10.0
but when i query for '003' it should show all coz no column is 0.0
UserId TradeId Trade Start Value End Value
003 106 Citibank 10.0 10.0
also since for '002' no column is all 0.0 it should display all columns
UserId TradeId Trade Start Value End Value
002 105 Citibank 10.0 09.0
002 107 Citibank 10.0 0.0
what i am currently using makes a temp table at runtime depending upon the null value columns and uses that runtime created table to display the results.. it was working fine but the moment the user limit reaches around 50 the query and retrieve time goes for a toss with CPU usage reaching around 98-99%
is there any query based solution for this type of a situation which retrieves selective fields
August 2, 2005 at 3:29 am
is there any query based solution for this type of a situation which retrieves selective fields
I can't think of any. This is normally an issue that should be solved by the client application, but I am guessing there is no client app here other than maybe some report showing the bare resultset returned by the proc? If you are using Excel or something you can try using VBA to get the result and do this processing (excluding columns) client-side before showing the results.
August 2, 2005 at 11:18 pm
Try this
Declare @userid int
Set @userid=001 -- Some UserId
Select UserId, TradeId,
Case
When Exists (Select * From Bank Where (StartValue =0 And EndValue = 0) And (UserId=@UserId)) Then Trade
When Exists (Select * From Bank Where (StartValue =0 And EndValue <>0) And (UserId=@UserId)) Then Trade,EndValue
When Exists (Select * From Bank Where (StartValue <>0 And EndValue = 0) And (UserId=@UserId)) Then Trade,StartValue
Else Trade,StartValue,EndValue
End
From Bank
Where UserId=@UserId
August 3, 2005 at 2:44 am
well venku009, i already have something similar to that running.. but my issue is that the this sample table has 5 columns... wherein the place i want to implement it has 39 table.. which means around 39 round trips/queires to teh database.... now for a few users it is fine but the expected user hit ratio seems to be around 150-200 which is killing the cpu and raising the load time to 450-500 secs for displaying the page...
i am using SQL Server 2000 with a classic ASP frontend and need a solution which will help me reduce load / query time on the database...
August 3, 2005 at 3:03 am
My assumption says that you are joining all the tables and placing the data into a temp table at runtime in a stored procedure(sp). If its true, and you are playing the above query on the temp table and even then your query is slow, then try using exec dynamic statement. Stream your query to exec statement. Like
Declare @userid int,@Str Varchar(8000)
Set @userid=001 -- Some UserId
Set @STR = '
Select UserId, TradeId,
Case
When Exists (Select * From #Bank Where (StartValue =0 And EndValue = 0) And (UserId=@UserId)) Then Trade
When Exists (Select * From #Bank Where (StartValue =0 And EndValue <>0) And (UserId=@UserId)) Then Trade,EndValue
When Exists (Select * From #Bank Where (StartValue <>0 And EndValue = 0) And (UserId=@UserId)) Then Trade,StartValue
Else Trade,StartValue,EndValue
End
From #Bank
Where UserId = ' + Convert(VarChar(6),@UserId)
Exec (@Str)
August 3, 2005 at 6:02 am
its a solution definately better than mine.. i had a colleague who came up with a solution which benchmarked faster and more load taking capacitythan our solutions... the logic was to retrieve the main resultset
UserId TradeId Trade Start Value End Value
001 101 Citibank 10.0 0.0
001 102 Citibank 10.0 0.0
001 103 HDFC 11.0 0.0
001 104 Citibank 10.0 0.0
and get the sum of the individual columns... i.e @totc1 = sum(c1), @totsumc2 = sum (c2), .....
after that generate/concat a query after checking the conditions if the column has to be included or not depending if the sum is 0 and execute the new generated query..
this reduces the time and load as the matching is done on a resultant set and there are only 2 queries to the database for a particular report display.. it excludes the complete overhead of the temp table or multiple querying to the database.....
Thanks a lot both of you for your inputs !!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply