Query med IN, input varchar

Problemet er at grundliggende er en parameter en enkelt vaerdi.

Og der er kun to muligheder dynamisk SQL eller EXEC (hvilket reelt ogsaa er dynamisk SQL).

Der er mange foer dig som har bandet over det.

okay ... jeg ville sådan set også bare viden hvor i verden jeg stod ...

Men jeg kan så forstå på dig at du ville gå efter mit sidste eksemple?

Jeg kan jo altid have koden ved siden af, så jeg undgår dumme fejl ... men DARN, det burde de da lige have taget med så man man havde en liste type ... øv

Men jeg er da glad for at jeg ikke er den eneste som har bandet over det. :-)

// ouT

Nå, men du må nok hellere smide et svar så.

Som et lille side spm:
Er der nogen performance gain ved at lave et "JOIN" over på en anden table med de ID's vs "IN" som jeg bruger her ?

mvh

En løsningsmulighed kunne være følgende:

Lav en hjælpetabel:

CREATE TABLE Numbers (
  number INT NOT NULL PRIMARY KEY
)

Fyld data i den:

DECLARE @i INT
SET @i = 0
WHILE @i < 10000
BEGIN
  INSERT INTO Numbers (number) VALUES (@i)
  SET @i = @i + 1
END

Lav følgende funktion:

CREATE FUNCTION [dbo].[fn_split]
(   
    @ordinal  varchar(max)   
)
RETURNS TABLE
AS

RETURN
(   
    SELECT
        SUBSTRING(@Ordinal+',', number, CHARINDEX(',', @Ordinal+',', number) - number) AS Number
    FROM Numbers
    WHERE
        number <= LEN(@Ordinal)
        AND SUBSTRING(',' + @Ordinal, number, 1) = ',' 
)

Du kan nu lave teste funktionen ved dette:

select number from dbo.fn_split('1,2,3,4,5')

Og det betyder, at du kan løse dit problem sådan her:

SELECT * FROM Players WHERE SID = @SID AND UID IN ( select number from dbo.fn_split(@IDS) );

Læs evt. lidt mere om hvad man kan bruge sådan en numbers hjælpetabel til: http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable

Hvis du bruger MS SQL 2005 skulle det så vidt jeg ved være muligt at lægge de ønskede ID'er i en xml streng og så sende den som parameter. Det giver mulighed for at sende en tabel, som parameter til en stored procedure.

Prøv evt. at søge lidt på det.

Wagner

Hej,

Tak for de yderligere muligheder ...

sjang:
Uden at have testet noget endnu, hvordan er performance så på det med hjælpe table ... ?

Vil man så stadig bruge "IN" ? Eller vil det være hurtigere med "JOIN" ? eller det er 100% samme operationer som bliver udført på SQL serveren.

wagner:
MSSQL 2008 ... sidste skud på stammen, så der skulle være alle mulighederne.
pt har jeg mine IDs som en List<int> i ASP.NET ... og at trylle den om til 12,13,15,13 er nemt ... men sql kan jo også være nemt ... man skal bare lige vide hvordan det gøres ...

Tak til alle ...
Jeg vil læse lidt om det ... inden jeg fortsætter mit arbejde ... specielt hvis nogen af de nye muligheder skulle give bedre performance :-) Performance er det vigtigste ... ellers havde jeg bare lavet en foreach omkring mine IDs i .NET og hente det ud på den måde ... men det er jo ik' sejt og smart *heheh*

Så venner ... kridt skoene ... hurtigste og bedst performance løsning.

Lige pt er der kun ca. 1 mill rows ... men det skulle gerne stige ...

mvh.

Når man laver en IN, så laver query optimizeren det rent faktisk om til en lang række OR statements. MEN hvis sql'en er "simpel" nok (uden nærmere at definere hvad det betyder), så laver den faktisk selv sql'en om til en join, som generelt er meget hurtigere. Hvis sql'en er for "kringlet", så er det ikke sikkert at optimizeren kan gennemskue at den lige så godt kunne lave en join, og så vil den gå efter OR-metoden. Så jo, lav endelig en join i stedet..

Du kan jo prøve at lave Numbers tabellen, samt funktionen - og så prøve at se hvordan det performer. Du kan evt manuelt fyre disse to af:

SET STATISTICS IO ON
SET STATISTICS TIME ON

og så bagefter fyre din sql af, som så bare indeholder en manuelt udfyldt varchar. Så får du at vide hvor mange IO'er der bliver læst, og hvor meget cpu-tid der går med kørslen.

Hej,

Jeg havde nemlig også fået forståelsen af at den lavede en masse "OR" ... og da mine quries lige pt er simple kunne jeg nemlig se i min query planer at den netop brugte indexs og var hurtig, derfor jeg var lidt kritisk overfor om "IN" var den rigtige måde ... da de kan gå hen og blive størrer, så jeg vil helt klart se på den funktion så jeg er sikker på at den joiner ... og ikke kun hvis den har lyst :-)

Jeg bruger også SQL Profiler ... men jeg vil da lige se på de 2 statistics ting ... men jeg går ud fra at man skal huske at slå dem fra igen eller gør den selv det ?

mvh

Når du slår disse statistics ting til, så er det kun gældende for din nuværende session. Så ja, enten slå dem fra manuelt ved:

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

eller bare slå din egen session ihjel.

Hej sjang,

Jeg kan jo ikke sige andet end det performer godt ...

Men jeg kan dog ikke lige gennemskue hvordan den function virker ...

Er der et sted hvor det er beskrevet mere i detaljer præcis hvad der sker? synes alt hvad jeg kommer frem til hele tiden ik' giver det resultat som den funktion faktisk gør ... argh, kan ik' lige gennemskue det.

// ouT

Jeg kommer lige med en forklaring senere i dag.

Hmm, jeg har forsøgt at skrive en forklaring, men det er faktisk ikke så nemt at formulere på skrift, uden at skulle skrive en længere afhandling ;)

Jeg vil derfor komme med et lidt mere simplificeret (og nok ubrugeligt) eksempel. Det kan forhåbentlig hjælpe tankegangen på vej mod hvad det er der rent faktisk foregår.

Prøv at fyre nedenstående af, og husk at substring tager 3 parametre, nemlig teksten, start-position og længde:

DECLARE @ordinal VARCHAR(max)
SET @ordinal = 'abcdefg'

SELECT LEN(@ordinal) AS length_of_ordinal

SELECT number FROM Numbers
WHERE number <= LEN(@ordinal)

SELECT SUBSTRING(@ordinal, 0, number)
FROM Numbers
WHERE number <= LEN(@ordinal)

SELECT SUBSTRING(@ordinal, number, 1)
FROM Numbers
WHERE number <= LEN(@ordinal)

Du kan fx prøve at google på "auxiliary numbers table".

Ja okay .. tror bare jeg lige pt acceptere at det virker ... og ved hvor jeg så kan finde mere information senere.

Du skal nok få lidt ekstra point, da dette lille spm har taget lidt mere med sig hele tiden. 200 stk.


Følgende er output fra en query der laver et par joins ... og som du kan se, bruger den numbers table.
SQL Server parse and compile time:
  CPU time = 1293 ms, elapsed time = 1293 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 1, logical reads 13031, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Numbers'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PlayerStats'. Scan count 1, logical reads 107, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Players'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Alliances'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 171 ms,  elapsed time = 174 ms.

SQL Server Execution Times:
  CPU time = 1468 ms,  elapsed time = 1470 ms.
SQL Server parse and compile time:
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

Men er lidt i tvivl om hvordan jeg skal tolke dens information for at spore mig ind på hvad der præcist er galt hvorfor helved den er så langsom ...

Tidligere har det nemlig kørt på ca. 200 ms ... og det her er jo helt galt ...

Jeg har også kørt en Execution Plan:
http://syska.dk/upload/trav-6.jpeg

Men jo meget tydeligt angiver at jeg har index ... kan bare ikke lige finde ud af hvad der sker ....


Næste spm.
Hvad er alle de statistics man kan oprette/slette under ens tables ... ? Kan se der bliver lavet en for hver index/key ... og så er der nogen: _WA_Sys_000xxxxxxxx

Her mit problem med execution time kan være ? Jeg er totalt lost og har siddet med dette problem det meste af dagen ... og er ved at gå helt amok ... :-(

Nå ... men håber du har en god forkalring.

mvh

Skulle måske siges at:
'Worktable' - aner ik' hvad det er for en table
'Numbers' - 10k rows
'PlayerStats' 1,5 mill rows
'Players' 170k rows
'Alliances' 10k rows

Den her tråd skyder så lidt ideen væk med at returnere en VALUED TABLE:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1673167&SiteID=17

Tror jeg skal over i noget processkeyed table ... :-(

// ouT

ALDRIG SEND EN varchar(max) til en sp ...

Efter jeg lavede den om til en varchar(1000) .... faldt det til den fra 1.5 sek til 100 ms ...
OMG ... kan du give en forklaring på det ?

Har godt nok læst at varchar(max) ligger gemt et andet sted, men at det kan have en så betyde performance issue var jeg ik' klar over :-s

Btw ... nu joiner jeg også på en process keyed table ...

Først:
INSERT INTO PK SELECT @@SPID, Number FROM dbo.fn_Split( @IDs );

og så min query efterfølgende ...

// ouT

nej ... var vist ikke rigtig alligevel ... nu ved jeg snart ikke hvad der fik den til det.

Men mange joins med VALUED TABLE skal vist ikke bruges i for komplekse joins ...

Jeg havde faktisk tænkt på at foreslå at gemme det i en midlertidigt tabel som du gør nu her, men jeg troede faktisk at optimizeren ville udføre det samme uanset om man gjorde det. Men det er nok lidt det samme som med IN-queries: Hvis sql'en er simpel, så kan optimizeren godt lave det effektivt, men hvis man vil være sikker, så lav en join. Det samme gælder sikkert når man joiner sådan en table valued function.

Og mht. varchar(max) i forhold til varchar(1000), så kender jeg ikke lige til om der skulle være performance forskel. Så der kan jeg ikke lige svare dig.

Men hvordan ser det ud nu - hvordan kører det?

Jeg var faktisk ved at skrive igen og det kører super ...

Endelig fik jeg styr på det, og lavet en del om, så man kan trække info ud om flere ID'er på samme tid ... typisk at folk kommer med flere ideer til ens ting :-)

Performance er fin synes jeg, set i forhold til antal rows ... men det kommer til at være mange flere når jeg lige får det hele fyldt ind :-) *heheh*

Men mht til set statistics io/time ON.

Hvad skal man så kigge efter ? Går ud fra at physical reads skal være lav ... og at logical reads ja, er fra index/ram ...

Så at overstående data info fra statistics er fin ... og at det så var optimizeren der fuckede det hele mht til execution time.

Sig også endelig til hvis du har andre gode ideer til hvordan man kan optimere ... links, hints etc.

// ouT

Ja physical reads er altid noget snavs, for det går jo pænt langsomt. En tommelfingerregel siger, at en moderne disk kan levere 150 random io'er pr. sekund. Så hvis den skal lave 1000 fysiske reads, ja så tager det jo noget tid. Og ja, de logiske reads kommer fra rammene.

Det man nogen gange kan få ud af disse statistics, er fx hvis en enkelt tabel har mange logiske reads... så KAN det nogle gange skyldes at der mangler et index. Hvis en enkelt tabel laver en del reads, så bør man i hvert fald lige sikre sig, at der er fornuftige indexes. Det jeg nok bruger disse statistics mere til, er til at sammenligne. Hvis du fx laver en lille rettelse i koden, laver et index eller lignende - så er det lettere at se den konkrete forbedring. Man vil så nemt kunne se hvor mange reads man sparer, samt forskellen i cpu-tiden.

Compile time kan også give et hint om man skal lave noget om. Hvis man fx bruger dynamisk sql (hvilket altid er en dårlig ide i forhold til performance), og den samlede kørselstid er 500 ms, så kan man se på hvor meget tid der går med at compile execution plan'en. Hvis der fx går 450 ms med at lave execution planen, så ville man kunne spare dem ved at undgå dynamisk sql. For så cacher sql serveren nemlig planen, og man sparer de 450 ms til at compile hver gang.

I forhold til at kigge på execution plan'en, så plejer jeg at kigge efter index scan's. Hvis den scanner et index, så kan det tyde på at man ville kunne lave et bedre index. Seeks er altid bedre end scans. Hvis der laves RID Lookups, eller Key Lookup's, så kan det også tyde på at man kan optimere sine indexes - fx ved at lave lade sit index inkludere de nødvendige kolonner. Søg fx efter covering indexes hvis det er nyt for dig at man kan lave indexes på mere end én kolonne.

Ellers har jeg vist ikke så meget at tilføje. Jeg smider lige et svar :)

Bestemt ikke nyt med index over flere columns, og at include colums som bare skal læses ... selvom jeg ikke så tit synes det er det værd.

så det bruger jeg RIGTIG meget ... og synes selv at min viden går i den rigtige retning :-)

Man begynder også først at kunne mærke forskel på om ens database er lavet rigtig når der kommer noget data i den :-) Derfor dette lille hobby projekt er fedt.

http://www.eksperten.dk/spm/851158 - og her et ekstra point for mere udbybende svar end spm startede med.

// ouT