I have just learnt about temp tables and the syntax needed to insert data into created temp tables but I have been told that I can also use temp tables rather than subqueries which is what I am currently doing and the script takes a long time to run.
How would I go about creating these tables when I don't want to list the thousands/millions of values to insert but rather run a query and have that made into a temp table?
Hopefully that makes sense, would love some advice!
Thanks,
Just get what you really need and filter it down before dumping data into #temp table.
create table #whitenile(CompanyId int, TotalEmployees int)
insert into #whitenile
select CompanyId, count(TotalEmployees )
where DateColumn between '2016-01-01' and '2016-12-31'
and CityName = 'Kigali'
group by CompanyId
Also add some indices to the temp table and you should be good to go. But please do provide some sample data and your requirements.
create table #whitenile(CompanyId int, TotalEmployees int)
insert into #whitenile
select CompanyId, count(TotalEmployees )
From dbo.BujuBanton
where DateColumn between '2016-01-01' and '2016-12-31'
and CityName = 'Kigali'
group by CompanyId
I stuck a "from table" in here and it worked, usually my scripts are a lot more complex so will test them too but all looks very good so far, thanks for the expert advice
create table #whitenile(CompanyId int, TotalEmployees int)
insert into #whitenile
select CompanyId, count(TotalEmployees )
From dbo.BujuBanton
where DateColumn between '2016-01-01' and '2016-12-31'
and CityName = 'Kigali'
group by CompanyId
I would only use a #TempTable in that situation if I wanted to use the data repeatedly - e.g. in multiple queries - or for "further processing"
If it is just a single query it will usually execute faster as a single query, with a sub-query, rather than using a #TempTable as an intermediate step.
if you have a slow query I suggest you post that here and ask for advice on "tuning" it.
I have been working on one today which hasn't run, this wasn't actually the one that I was initially writing about but maybe will serve as an example. Each individual subquery runs quickly but it is when I consolidate them that it doesn't work.
In the first subquery Sep17YTDOffers, I am trying to get the number of offers for Sep17 so far. In the second, I am trying to get the total Sep16 offers and then in the third the number of Sep16 offers at this time last year. I need to use all of these numbers to look at performance against targets.
WITH Sep17YTDOffers as (
select S.client_id, S.crms_number, last_mo_source_tag from dbo.ZZ_crms_student_tag ST
left join dbo.ZZ_crms_student S ON S.client_id = ST.client_id AND S.crms_number = ST.crms_number
where S.client_id IN ('Client A', ' Client B', ' Client C') and dead = 0 and last_mo_source_tag like '%sep%' and last_mo_source_tag like '%17%'),
Sep16TotalOffers as(
select S.client_id, S.crms_number, last_mo_source_tag from dbo.ZZ_crms_student_tag ST
left join dbo.ZZ_crms_student S ON S.client_id = ST.client_id AND S.crms_number = ST.crms_number
where S.client_id IN ('Client A', ' Client B', ' Client C') and dead = 0 and last_mo_source_tag like '%sep%' and last_mo_source_tag like '%16%'),
Sep16YTDOffers as (
select Sep16.client_id, Sep16.crms_number from (SELECT
sth.client_id,
STH.crms_number,
STH.tag_type,
STH.tag_value,
ROW_NUMBER() OVER (PARTITION BY sth.client_id, STH.crms_number
ORDER BY sth.client_id, STH.crms_number, STH.date_created asc) seq,
STH.date_created
FROM dbo.ZZ_crms_student_tag_history STH
left join dbo.ZZ_crms_student s on s.client_id = STH.client_id and s.crms_number = STH.crms_number
WHERE sth.client_id IN ('Client A', ' Client B', ' Client C')
AND STH.tag_type = 'Made Offer Source' and dead = 0
AND STH.tag_value LIKE '%Sep%' and STH.tag_value LIKE '%16%'
AND STH.date_created < dateadd(yy, -1, getdate())) Sep16
where Sep16.seq = 1),
FINAL
AS (SELECT
S17YTDO.client_id,
count(S17YTDO.crms_number) as [Sep17 YTD Offers],
count(S16TO.crms_number) as [Sep16 Total Offers],
count(S16YTDO.crms_number) as [Sep16 YTD Offers]
FROM Sep17YTDOffers S17YTDO
LEFT JOIN Sep16TotalOffers S16TO ON S16TO.client_id = S17YTDO.client_id
LEFT JOIN Sep16YTDOffers S16YTDO ON S16YTDO.client_id = S17YTDO.client_id
group by S17YTDO.client_id)
SELECT
F.client_id,
F.[Sep17 YTD Offers],
F.[Sep16 Total Offers],
F.[Sep16 YTD Offers]
FROM FINAL F
Those sort of wildcard tests are deadly (performance-wise). Less bad is a "starts with test" e.g. LIKE 'sep%' but better is an Equals, or a Range (assuming that a suitable index already exists, or could be created)
In case it is important to you please be aware that
dateadd(Year, -1, getdate())
is this day, month and time last year. That is to say: to the exact millisecond.
if what you actually need is "anything on [any time] this day last year, and thereafter" or "Anything after 01-Jan [or some other anniversary date] last year" there are easy ways of calculating that start-point-date, and making sure that the time element is midnight.
By the by, I recommend that you use the more verbose date part names - e.g. "Year" instead of "yy", the shorthands are a source of bugs / misunderstandings when code is maintained in the future.
If you saw an abbreviated date part starting with "m" would you know if it was Month? Minute? Millisecond? Microsecond? "n" = Nanosecond? Nope, its minute. "dy" = Day? Nope. That's day-of-the-year. "y" = Year? Nope, that's day-of-the-year too ... hobby-horse of mine, 'case you hadn't noticed
Thanks @scarela, that field appears in the student tag table and doesn't appear in other tables which is why I didn't make it 'st.last_mo_source_tag'. Is that what you mean/am I understanding you correctly?
It would be better to store the TAGs as a Child Table, one tag-record per row. Easy to say .. might not be easy to do of course!
Also
last_mo_source_tag like '%sep%'
Will match any tag-value that CONTAINS "sep" - which might not be what you want, so you might need
' ' + last_mo_source_tag + ' ' like '% sep %'
if they are space-separated (can do similar for a different delimiter, like "," for example). That will run even slower
This sort of ambiguity comes about whenever the database structure is bypassed and columns are overloaded with multiple values. The typical one is Users who tell me they want a reference "XXX123YYY" were "XXX" is the company/geographic-area/etc., 123 is the actual, unique, reference, and "YYY" is the person handling that business - or some other data-attribute. What they should really be using is "123", but when they write the Reference on a paper-file / email it carries with it all the other useful information. Trouble is, it changes - and the historic emails never do, nor does, usually, the physical file wrapper ...
Store it how the database would prefer to have it, and performance will improve (along with data integrity!)
You could still have the [last_mo_source_tag] column in "human readable form", just add a Trigger to the table which "splits" the [last_mo_source_tag] value on its delimiter, and stores the values in a child-table. You can then use that for query purposes, and the user-form / other code won't need to change.
