Can this SQL be improved?

The following query adds it's results into a table variable. The key goal of this table is to contain a) the number of items that are within the set b) the complete number of items over all sets. The set is set by g_kerntaak_fk.

I have used a subquery which contains the group by clause to find b). Something tells me that this query can still be improved but I can't find it.... currently this query takes 42% of the batch....
declare @tblComponentWeegschaal table(g_kerntaak_fk uniqueidentifier not null, g_component_fk uniqueidentifier not null, i_aantalinkerntaak int not null, i_aantalinprofiel int not null, f_weegschaal float not null)
insert into @tblComponentWeegschaal (g_kerntaak_fk, g_component_fk, i_aantalinkerntaak, i_aantalinprofiel, f_weegschaal)
select		cku.g_kerntaak_fk, cku.g_component_pk, cku.ingebruik as aantalinkerntaak, totaal.aantalinprofiel, (1/cast(totaal.aantalinprofiel as float))*cku.ingebruik
from		bpsvwComponentenKerntaakUsage cku
inner join	bps_competentiemanagement_scankerntaak cmk on cmk.g_kerntaak_fk = cku.g_kerntaak_fk
inner join	(
	select		g_kwalificatieprofiel_version_pk, cku.g_component_pk, sum(ingebruik) as aantalinprofiel
	from		bpsvwComponentenKerntaakUsage cku
	inner join	bps_competentiemanagement_scankerntaak cmk on cmk.g_kerntaak_fk = cku.g_kerntaak_fk
	where		cmk.g_competentiemanagement_fk = @g_competentiemanagement_fk
	group by	g_kwalificatieprofiel_version_pk, g_component_pk
) totaal on cku.g_component_pk = totaal.g_component_pk
where		cmk.g_competentiemanagement_fk = @g_competentiemanagement_fk

Open in new window

LVL 22
CJ_SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
I think so.  First, if you have a large number of records, use a temp table instead of a table varaible for @tblComponentWeegschaal .  also, you will probably see performance gains by putting the subquery from your join statement into a temp table and placing an index on it.  Also, make sure the fields you are joining on have indexes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CJ_SAuthor Commented:
Thank you very much. The temporary table makes quite the difference. The sets were about 4000 each - and the load is now down a few notches.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.