September 14, 2007 at 7:39 am
I feel the issue i going to be directly related to improper plan being cached first and foremost. See if it improves by addin the
WITH RECOMPILE
option just before the AS and after you variable collection. There are other things as stated with redesigning the process but for the short term to see if you can improve with that.
September 14, 2007 at 9:02 am
never mind someone already mentioned this to you.
September 14, 2007 at 10:23 pm
I don't mind because these were my own comments. Temp tables with joining also sucks. Using temp tables the i can't produce indexing to joing them with other tables. Need comments here ....
Shamshad Ali.
September 15, 2007 at 7:26 am
You CAN build indexes on temp tables. You just have to build them each time you build the table. You'd also want to tear them down when you're done.
This also brings up something else - there are "temp tables" and there are tables storing temp data. There's nothing preventing you from creating a table (a "permanent" one) whose sole purpose is to be used to store "temporary" data while you build this sum. With indexes.
There are also several ways to instantiate "temp tables", each with their own pluses and minuses.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 16, 2007 at 8:40 pm
You mean each time (1 or many) user(s) want data based on their requirement, my proc. first create temp tables, create indexes and then push billions of records into it, and then I use join with my small lookup tables and finally return to endusers?
Using temp tables is the final solution?
My initial query (plz. see first page) was, I wanted to know how can i produce dynamic WHERE clause based on many combinations from user interface.
Do I need to create many stored procedures for each and every combincation of CASE(s) like i said:
1- FromDate
2- ToDate
3- FirstName
4- LastName
5- UserID
6- Administration
7- Region
8- Facility
9- Location
10- Topic
11- Title
12- Media
User can search for FromDate and ToDate and FirstName only, user Can search FromDate and ToDate or FirstName only, User can search Media title with LastName only. etc etc. These all combinations vary. and I have to use them in WHERE. how others are handling such kind of requirements?
I can't put these all columns values in one column to make it possible that all data can be search in one column and the Wwill be in one columns.
Where administration require join with administration table if a user search for Facility with LastName i have to join with Facility and put where for Facility Columns.
Hope some one clearly understand the problem and
September 16, 2007 at 9:27 pm
WHERE
(YourDate >= @FromDate OR @FromDate IS NULL)
AND
(YourDate < @ToDate OR @ToDate IS NULL)
AND
(FirstName = @FirstName OR @FirstName IS NULL)
.....
etc.
_____________
Code for TallyGenerator
September 17, 2007 at 5:48 am
First of all, I don't believe you have posted any of the details about how many rows are in any of you table, or about the users. Just that you are having a performance issue with a complicated procedure.
The suggestions so far have been:
There have been other suggestions, but at the moment that isn't my point. The correlated sub-queries (I believe on the LOOKUP tables) use group by's. The suggestion is to create temp tables for those, and create a primary key on the group by field. This will also create a unique index that will aid in joins to the bigger tables.
It sounds like you understand this pretty well, just a thought. If you think it sounds wrong what you think you read, you probably misunderstood, or it is because you know something about your environment that we don't.
Also, if you have that many users going after that much data but you can't precalculate some of the data, perhaps you need to look at it very differently to find a way , or look into buying a bigger system.
September 17, 2007 at 5:53 am
As for dynamic where (if you must). I personally would suggest doing this with dynamic sql and sp_executeSQL with parameters for those fields that you end up supplying.
At the very least you will create som fairly standard SQL that can be optimized. You will stand a much better chance in getting that to perform well than you will with a procedure with a where clause like that.
September 17, 2007 at 10:47 pm
Thanks for your all replies and suggestions (may be I'm missing someone's comments).
I still prefere dynamic SQL in my case because I tried following, but as you put all cases in WHERE (even it is not supplied or required by user - lets say user don't want to search with UserID) but the WHERE will be calling each and every required field if not input then it will be in OR field IS NULL.
WHERE
(YourDate >= @FromDate OR @FromDate IS NULL)
AND
(YourDate < @ToDate OR @ToDate IS NULL)
AND
(FirstName = @FirstName OR @FirstName IS NULL)
That again sucks alot, the basic reason I think is becuase if a WHERE COL_Name is not required why we put it in our query, just to make it as a ONE query for every request comes from User INPUT....
Shamshad Ali.
September 19, 2007 at 10:46 pm
Hi,
please try this option
You are doing the left join tbl_tracking_mstr with tbl_cv_user instead of you can do inner join,try to avoid left join
for the library visit count and channel visit count,calculate separately say store the result in temp table then do the inner join tracking master. ensure that correct index exist what ever criteria you are using the where clause
September 21, 2007 at 9:25 am
Just a couple of quick suggestions that may help somewhat.
I've had performance gains with the code suggested above
WHERE (YourDate >= @FromDate OR @FromDate IS NULL)
by changing the order to checking the variable for Null BEFORE comparing to the field
WHERE (@FromDate IS NULL Or YourDate >= @FromDate)
Also, doing the Sum(IsNull(Field,0)) repeatedly. The SUM function is supposed to ignore Null values, so I think there's quite a few IsNull calls that can be avoided in the statement.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply