Kad zamenim privremenu tabele sa tabelom-promenljvom vreme kreiranje tabele se značajno produži.
Privremena tabela #relevantrisk
Code:
CREATE TABLE #relevantrisk (pk int,[Channel] varchar(50), [Sub-Channel] varchar(50),
PaymentMethod varchar(50), [Name] varchar(250), HistoryActionType nvarchar(50), TransEffDate datetime, IPTPercent real )
INSERT INTO #relevantrisk (
pk,
Channel,
[Sub-Channel],
PaymentMethod,
[Name],
HistoryActionType,
TransEffDate,
IPTPercent)
SELECT ...
...
DROP TABLE #relevantrisk
CREATE TABLE #relevantrisk (pk int,[Channel] varchar(50), [Sub-Channel] varchar(50),
PaymentMethod varchar(50), [Name] varchar(250), HistoryActionType nvarchar(50), TransEffDate datetime, IPTPercent real )
INSERT INTO #relevantrisk (
pk,
Channel,
[Sub-Channel],
PaymentMethod,
[Name],
HistoryActionType,
TransEffDate,
IPTPercent)
SELECT ...
...
DROP TABLE #relevantrisk
Vrsta osiguranja Period Slogova Select (s) Vreme kreiranja tabele (s)
Home 01-05 do 11-05 133 21 21
Home 01-05 do 31-05 317 23 37
Motor 01-05 do 11-05 120 21 25
Tabela promenljiva @relevantrisk
Code:
DECLARE @relevantrisk table (pk int,[Channel] varchar(50), [Sub-Channel] varchar(50),
paymentmethod varchar(50), [Name] varchar(250), HistoryActionType nvarchar(50), TransEffDate datetime, IPTPercent real )
INSERT INTO @relevantrisk (
pk,
Channel,
[Sub-Channel],
PaymentMethod,
[Name],
HistoryActionType,
TransEffDate,
IPTPercent)
SELECT ...
DECLARE @relevantrisk table (pk int,[Channel] varchar(50), [Sub-Channel] varchar(50),
paymentmethod varchar(50), [Name] varchar(250), HistoryActionType nvarchar(50), TransEffDate datetime, IPTPercent real )
INSERT INTO @relevantrisk (
pk,
Channel,
[Sub-Channel],
PaymentMethod,
[Name],
HistoryActionType,
TransEffDate,
IPTPercent)
SELECT ...
Vrsta osiguranja Period Slogova Select (s) Vreme kreiranja tabele (s)
Home 01-05 do 11-05 133 21 161
Home 01-05 do 11-05 317 23 470
Motor 01-05 do 11-05 120 21 520
Prema onome što ja znam trebalo bi da su perfomanse bolje za tabelu-promenljivu. Upit koji sledi na osnovu tabele jeste duplo brži u slučaju tabele-promenjljive ali to nije dovoljno da nadoknadi vreme utrošeno prilikom kreiranja. Da li ima neko objašnjenje? Nisam još imao prilike da bazu prebacim na drugo okruženje i da probam. U SELECT upitu uspostavlja se veza sa bazom na drugom serveru.
Nije to loše Rembrante, samo što ne bi dodao još malo boje?