Please, help with a complicated query? 2

[esmithbda]

I am trying to move from a stored procedure which returns a single row and is called many times from the client side, to instead take the data from the client and perform all of the work server-side instead.

To call the stored procedure many times from the client, getting one row back, for our largest possible dataset takes roughly 1 minute to complete.

I thought for sure moving that over to a stored procedure on the database server and handling it in there would make it faster. But the largest dataset takes exactly 1 minute to complete.
It is probably still faster than with the client side solution - but not nearly as fast as I thought it would be.

The server hardware is not the issue. We have a quad processor (well, dual HT Xeon P4s, which is the OS sees as quad CPU), 4GB RAM, and multi high speed SCSI HDs - I have watched the load on the machine during this query and the server just yawns. The load on any processor never goes over 25% and the memory used is only about 400MB (and doesn't go up or down due to the query).

The dataset returned is only about 500 rows. The largest table we look at has fewer than 12K rows in it. So as far as I am concerned, this is a tiny database.

The query I need to run pulls data from a 5 database tables (well, one is a view) and 1 table variable that is created earlier in the stored procedure.
The table variable is created very quickly - I have tested that part by commenting out everything after it and then tracking how long that takes (less than one second).


So the bottleneck is all in this query here:

	SELECT 
		au.UserID UserID,
		au.CompanyID CompanyID,
		u.long_name UserName, 
		sc.description FLongName, 
		cd.eq_type EQ_Type, 
		cd.s_decimals SDecimals,
		cd.p_decimals PDecimals,
		dbo.fn_round(
sum(TSR.TSR_NUMBER),
cd.S_DECIMALS,
cd.s_rounding) 
TSRCount,
		cd.s_rounding SRounding

	FROM 
		@AllUsers au, 
		Users u, 
		v_eq_attr eaid,
		sub_company sc,
		company_definitions cd,
		t_s_r tsr
	WHERE
		u.user_id = au.UserID
		AND
		eaid.company_id = au.CompanyID
		AND
		sc.sc_id = eaid.sc_id
		AND
		cd.company_id = au.CompanyID
		AND
		tsr.company_id = au.CompanyID
		AND
		tsr.sc_id = eaid.sc_id
		AND
		tsr.user_id = au.UserID
		AND
		tsr.s_date <= @DateTo
		AND
		tsr.s_date >= '1/1/1990'
		AND 
		tsr.Status='A'

	GROUP BY
		au.UserID,
		u.long_name,
		cd.eq_type,
		sc.description,
		cd.s_decimals,
		cd.p_decimals,
		ai.CompanyID,
		cd.s_rounding
	HAVING 
		dbo.fn_round(sum(TSR.TSR_NUMBER),cd.S_DECIMALS,cd.s_rounding) <> 0.0

I have changed the name of the tables and fields so that it is abstracted from what our data is (legal contract issue) - but the query is still the same.

Could anyone please suggest how to increase performance on this?
I am aware that I am using the "old" Microsoft way of doing joins - but I am not SQL savvy enough to know how to do a 5 table join the ANSI way - so if you think that would make a difference, then I am all ears (but will need some assistance as to how that is formed).
Also, do you think it would be possible that subqueries would be faster? If so could you please give an example of how that might look?

If you want a description of how any given table looks in terms of how many columns, or how many rows it is likely to have, I can give more detail.

Thanks for any help - I'm already losing my hair, no need to pull it out as well

 

[ProdAir]

off hand, without totally understanding what you want to accomplish, I would say the user-defined function is probably one of your major issues (dbo.fn_round). Also, you want to make sure the 5 tables have the right indexes created to work with this query.

I didn't really understand your explanation of what you are trying to accomplish so I can only base my opinion on what I saw in your query.

 

[hmckillop]

Well for starters, your fn_ruond is being called for all rows returned but I assume all you wish to do is round the final result?

What indexes do you have on the query, that will probably give you the biggest return in speed.


your query re-written in ANSI shouldnt make too much of a difference in performance though here is anyway.

SELECT 
        au.UserID UserID,
        au.CompanyID CompanyID,
        u.long_name UserName, 
        sc.description FLongName, 
        cd.eq_type EQ_Type, 
        cd.s_decimals SDecimals,
        cd.p_decimals PDecimals,
        dbo.fn_round(
sum(TSR.TSR_NUMBER),
cd.S_DECIMALS,
cd.s_rounding) 
TSRCount,
        cd.s_rounding SRounding

    FROM Users u
	INNER JOIN @AllUsers au on u.[user_id] = au.UserID
	INNER JOIN v_eq_attr eaid on eaid.company_id = au.CompanyID
	INNER JOIN sub_company sc on sc.sc_id = eaid.sc_id
    INNER JOIN company_definitions cd on cd.company_id = au.CompanyID
    INNER JOIN t_s_r tsr on tsr.company_id = au.CompanyID and tsr.sc_id = eaid.sc_id and  tsr.user_id = au.UserID 
    WHERE
        tsr.s_date between '1/1/1990' AND @DateTo
        AND tsr.Status='A'

    GROUP BY
        au.UserID,
        u.long_name,
        cd.eq_type,
        sc.description,
        cd.s_decimals,
        cd.p_decimals,
        ai.CompanyID,
        cd.s_rounding
    HAVING 
        dbo.fn_round(sum(TSR.TSR_NUMBER),cd.S_DECIMALS,cd.s_rounding) <> 0.0

An index ensuring joins are ok and one ok tsr.s_date and tsr.t_status would help

"I'm living so far beyond my income that we may almost be said to be living apart

 

[esmithbda]

Thanks for the tips!

ProdAir:
As for what I am trying to accomplish, this query currently runs far slower than I would think it should. So I am trying to resolve if I am just thinking incorrectly, or if the code is bad. If the code is bad, then I would like suggestions on how to improve it.

I removed the function call from the query (figuring I could handle the same thing on the client side) and it ran in exactly the same amount of time. (technically 2 seconds slower, but it it varies a little each time anyway)
So it appears that isn't the bottleneck.

I went through and made sure there were indexes for each point in each table as you suggested.
The table variable can't have indexes, and then there are 2 views (I said 1 at first, but there are 2) which won't let me add indexes.
Of the 3 tables, only the TSR one didn't have some potential indexes. I added them in and then reran the query.
It still took 1 minute to run.

After adding/editing indexes, does the SQL Server process need to be restarted?
I'm just a programmer and don't normally have to do the DBA side of things, but we are a small shop so I am now getting to do it all.
It is also feasible that I am not doing the indexes correctly (the tables and database were setup by an outside group, and I am writing reports to pull data out of them).

hmckillop:
I haven't tried rewriting the query in ANSI format yet, but as you say that is probably not likely to change much - especially in light that the other things haven't.

 

[esmithbda]

Okay, both of you were extremely helpful because it appears that there was nothing egregiously wrong in my code. That was a huge help in knowing that I should look more on the database side.

I edited/added indexes, and even restarted the SQL Service - but seemingly no matter what I did, the stored procedure would still take 1 minute to run. But any given query in it was quite fast, which made me think there was a larger issue.

So in the Query Analyzer I turned on the Execution Plan, the Trace, and the Statistics. The main one of those being the Execution Plan.

In there, I could see where the major bottlenecks were and it is extremely helpful. I had been looking in there before, but somehow didn't see the issue until after adding in the indexes.
After that I saw that the issue appeared to be due to using two views instead of raw tables. The views wouldn't allow me to use indexes - so I looked more closely at what the Execution Plan told me it was trying to do.

Two of the views were trying to pull out 577 rows of data out of 12M rows.
I looked at what I needed from the view and then rebuilt the important parts of that as a normal query and then added that into the code from above.

I ran the code and instead of 1 minute, which I knew had to be something wrong - it now takes 4 seconds for the same query.

The best part is that I can see there are still some points of optimization and I will try to get that time down even more.

I appreciate the efforts and they helped me learn more about what I was seeing - thanks!

 

[ProdAir]

Thanks for the stars and eventhough we didn't give you all the answers you needed, I'm glad it somehow got you on the right path.

 

[hmckillop]

Thanks also.
One thing in future if you have a problem with a view, is you might want to have an indexed view, it can provide huge gains in performance, but sometimes the overhead can make it not worth it.
Thanks again.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[esmithbda]

We might need an indexed view in the future. The views resolve permissions for a user as to whether or not they can view certain parts of the table (say a table is full of clients and one person in your company is only allowed to view data relating to 3 of those companies, and someone else might only see another 3).

For this particular report, it shouldn't matter - at least not for a year or so - so for now this should work. If and when we need a view, I will have to rewrite it as an indexed view - good to know, thanks.
(the database is ours, but the setup is part of a software package we bought which handles the input side - so any table/index setup was done by them and any changes I have to log so that if we update this in the future, my changes aren't wiped out - or are at least easily reimplemented)