[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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

0
CJ_S
Asked:
CJ_S
1 Solution
 
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now