MSSQL/ADO.NET] Parameters.AddWithValue() probleem

[Verwijderd]

Overal wordt terecht gepushed richting geparametriseerde queries i.v.m. SQL injection en ook query performance, en bij .NET en MSSQL kom je dan al gauw uit bij SqlCommand.Parameters.AddWithValue(parameter, waarde).

Mooi, en werkt ook goed, maar i.c.m. MSSQL blijkt dit bij string parameters nogal slecht te performen. De meeste string columns in MSSQL databases zijn nog steeds ANSIString, en geen Unicode, en AddWithValue() gaat bij .NET strings default uit van Unicode. En de conversie die MSSQL 2005 (nog niet getest bij 2008) dan moet doen blijkt erg duur te zijn.

Door i.p.v. AddWithValue() bij strings zelf een SqlParameter aan te maken, z'n DbType op AnsiString te zetten, en z'n ParameterName en Value te zetten (en 'm daarna natuurlijk aan Parameters te adden) konden wij bij ettelijke queries meer dan 10x aan performance winnen.
Blijkbaar vooral omdat MSSQL veel beter met z'n nonclustered idexes om kan gaan wanneer de parameter in 't zelfde formaat is als de column.

Voorbeeldje: met een nvarchar(8) (=unicode) parameter 150 page reads, met een varchar(8) (=ansi) 11 page reads. Terwijl de data niet varieerden en de indexen (voor ansi) gewoon 100% klopten.

[whoami]

Je hebt meer dan die 'AddWithValue' method.
Ik gebruik meestal de method 'Add' die je ook het type van de parameter laat specifieren.

Bv;

code:

cmd.Parameters.Add ("@someparam", SqlDbType.VarChar).Value = theValue;

Maar, wat is je vraag verder eigenlijk ?

[ Voor 9% gewijzigd door whoami op 24-09-2008 09:52 ]

[Verwijderd]

Geen vraag, maar een constatering van een probleem en een gevonden oplossing waar andere tweakers misschien ook iets aan kunnen hebben.

In ons geval heeft deze simpele tweak ervoor gezorgd dat bij een grote klant de twijfels over de schaalbaarheid van MSSQL kon worden weggenomen. De hoofdapplicaties (in Delphi/Win32/ADO) performen gewoon prima, maar onze .NET data access layer (toegepast in diverse webservices en webservice consumers) gebruikte AddWithValue(), en dat bracht soms de database response tijden drastisch omlaag, met soms zelfs timeouts.
Hun centrale database draait op een failover cluster met MSSQL 2005 Enterprise, 2 quad core processoren, 32GB RAM en een snel RAID 5 SCSI systeem, dus hun twijfels waren volkomen terecht.

[whoami]

Ah, op die manier.
Ik heb een aantal jaren geleden eigenlijk hetzelfde geconstateerd. Het is idd gewoon best dat je het type v/d parameter ook mee specifieert, en de makkelijkste manier om dat te doen is imho gebruik te maken van de Add(paramname,paramtype) member.