January 8, 2013 at 5:44 am
To have a stored procedure or not to have a stored procedure {yawn}? Yes we are still having this conversation. I am dealing with non-relational data i.e. columns with names STRING1-200, NUM1-50 etc. Users have the ability to retrieve and order by any combination of these fields making a large number of potential combination of queries. Developers have used two techniques for this LINQ to SQL and generating the SQL dynamically application side. This is causing the issues you'd expect.
Open discussion and thoughts from anyone please? Previous experiences and ideas on approach would be appreciated.
January 8, 2013 at 7:45 am
Sounds like you need to get a better understanding of how to generate catch all queries which is what it sounds like you are running here. Take a look at Gail's article about this topic. It explains how to use parameterized dynamic sql for exactly this sort of thing. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2013 at 7:57 am
To give more context this is my table.
CREATE TABLE [dbo].[LabelValues](
[UserAccountID] [int] NOT NULL,
[ContactID] [int] NOT NULL,
[STRING1] [nvarchar](255) NULL,
[STRING2] [nvarchar](255) NULL,
[STRING3] [nvarchar](255) NULL,
[STRING4] [nvarchar](255) NULL,
[STRING5] [nvarchar](255) NULL,
[STRING6] [nvarchar](255) NULL,
[STRING7] [nvarchar](255) NULL,
[STRING8] [nvarchar](255) NULL,
[STRING9] [nvarchar](255) NULL,
[STRING10] [nvarchar](255) NULL,
[STRING11] [nvarchar](255) NULL,
[STRING12] [nvarchar](255) NULL,
[STRING13] [nvarchar](255) NULL,
[STRING14] [nvarchar](255) NULL,
[STRING15] [nvarchar](255) NULL,
[STRING16] [nvarchar](255) NULL,
[STRING17] [nvarchar](255) NULL,
[STRING18] [nvarchar](255) NULL,
[STRING19] [nvarchar](255) NULL,
[STRING20] [nvarchar](255) NULL,
[STRING21] [nvarchar](255) NULL,
[STRING22] [nvarchar](255) NULL,
[STRING23] [nvarchar](255) NULL,
[STRING24] [nvarchar](255) NULL,
[STRING25] [nvarchar](255) NULL,
[STRING26] [nvarchar](255) NULL,
[STRING27] [nvarchar](255) NULL,
[STRING28] [nvarchar](255) NULL,
[STRING29] [nvarchar](255) NULL,
[STRING30] [nvarchar](255) NULL,
[STRING31] [nvarchar](255) NULL,
[STRING32] [nvarchar](255) NULL,
[STRING33] [nvarchar](255) NULL,
[STRING34] [nvarchar](255) NULL,
[STRING35] [nvarchar](255) NULL,
[STRING36] [nvarchar](255) NULL,
[STRING37] [nvarchar](255) NULL,
[STRING38] [nvarchar](255) NULL,
[STRING39] [nvarchar](255) NULL,
[STRING40] [nvarchar](255) NULL,
[STRING41] [nvarchar](255) NULL,
[STRING42] [nvarchar](255) NULL,
[STRING43] [nvarchar](255) NULL,
[STRING44] [nvarchar](255) NULL,
[STRING45] [nvarchar](255) NULL,
[STRING46] [nvarchar](255) NULL,
[STRING47] [nvarchar](255) NULL,
[STRING48] [nvarchar](255) NULL,
[STRING49] [nvarchar](255) NULL,
[STRING50] [nvarchar](255) NULL,
[STRING51] [nvarchar](255) NULL,
[STRING52] [nvarchar](255) NULL,
[STRING53] [nvarchar](255) NULL,
[STRING54] [nvarchar](255) NULL,
[STRING55] [nvarchar](255) NULL,
[STRING56] [nvarchar](255) NULL,
[STRING57] [nvarchar](255) NULL,
[STRING58] [nvarchar](255) NULL,
[STRING59] [nvarchar](255) NULL,
[STRING60] [nvarchar](255) NULL,
[STRING61] [nvarchar](255) NULL,
[STRING62] [nvarchar](255) NULL,
[STRING63] [nvarchar](255) NULL,
[STRING64] [nvarchar](255) NULL,
[STRING65] [nvarchar](255) NULL,
[STRING66] [nvarchar](255) NULL,
[STRING67] [nvarchar](255) NULL,
[STRING68] [nvarchar](255) NULL,
[STRING69] [nvarchar](255) NULL,
[STRING70] [nvarchar](255) NULL,
[STRING71] [nvarchar](255) NULL,
[STRING72] [nvarchar](255) NULL,
[STRING73] [nvarchar](255) NULL,
[STRING74] [nvarchar](255) NULL,
[STRING75] [nvarchar](255) NULL,
[STRING76] [nvarchar](255) NULL,
[STRING77] [nvarchar](255) NULL,
[STRING78] [nvarchar](255) NULL,
[STRING79] [nvarchar](255) NULL,
[STRING80] [nvarchar](255) NULL,
[STRING81] [nvarchar](255) NULL,
[STRING82] [nvarchar](255) NULL,
[STRING83] [nvarchar](255) NULL,
[STRING84] [nvarchar](255) NULL,
[STRING85] [nvarchar](255) NULL,
[STRING86] [nvarchar](255) NULL,
[STRING87] [nvarchar](255) NULL,
[STRING88] [nvarchar](255) NULL,
[STRING89] [nvarchar](255) NULL,
[STRING90] [nvarchar](255) NULL,
[STRING91] [nvarchar](255) NULL,
[STRING92] [nvarchar](255) NULL,
[STRING93] [nvarchar](255) NULL,
[STRING94] [nvarchar](255) NULL,
[STRING95] [nvarchar](255) NULL,
[STRING96] [nvarchar](255) NULL,
[STRING97] [nvarchar](255) NULL,
[STRING98] [nvarchar](255) NULL,
[STRING99] [nvarchar](255) NULL,
[STRING100] [nvarchar](255) NULL,
[STRING101] [nvarchar](255) NULL,
[STRING102] [nvarchar](255) NULL,
[STRING103] [nvarchar](255) NULL,
[STRING104] [nvarchar](255) NULL,
[STRING105] [nvarchar](255) NULL,
[STRING106] [nvarchar](255) NULL,
[STRING107] [nvarchar](255) NULL,
[STRING108] [nvarchar](255) NULL,
[STRING109] [nvarchar](255) NULL,
[STRING110] [nvarchar](255) NULL,
[STRING111] [nvarchar](255) NULL,
[STRING112] [nvarchar](255) NULL,
[STRING113] [nvarchar](255) NULL,
[STRING114] [nvarchar](255) NULL,
[STRING115] [nvarchar](255) NULL,
[STRING116] [nvarchar](255) NULL,
[STRING117] [nvarchar](255) NULL,
[STRING118] [nvarchar](255) NULL,
[STRING119] [nvarchar](255) NULL,
[STRING120] [nvarchar](255) NULL,
[STRING121] [nvarchar](255) NULL,
[STRING122] [nvarchar](255) NULL,
[STRING123] [nvarchar](255) NULL,
[STRING124] [nvarchar](255) NULL,
[STRING125] [nvarchar](255) NULL,
[STRING126] [nvarchar](255) NULL,
[STRING127] [nvarchar](255) NULL,
[STRING128] [nvarchar](255) NULL,
[STRING129] [nvarchar](255) NULL,
[STRING130] [nvarchar](255) NULL,
[STRING131] [nvarchar](255) NULL,
[STRING132] [nvarchar](255) NULL,
[STRING133] [nvarchar](255) NULL,
[STRING134] [nvarchar](255) NULL,
[STRING135] [nvarchar](255) NULL,
[STRING136] [nvarchar](255) NULL,
[STRING137] [nvarchar](255) NULL,
[STRING138] [nvarchar](255) NULL,
[STRING139] [nvarchar](255) NULL,
[STRING140] [nvarchar](255) NULL,
[STRING141] [nvarchar](255) NULL,
[STRING142] [nvarchar](255) NULL,
[STRING143] [nvarchar](255) NULL,
[STRING144] [nvarchar](255) NULL,
[STRING145] [nvarchar](255) NULL,
[STRING146] [nvarchar](255) NULL,
[STRING147] [nvarchar](255) NULL,
[STRING148] [nvarchar](255) NULL,
[STRING149] [nvarchar](255) NULL,
[STRING150] [nvarchar](255) NULL,
[STRING151] [nvarchar](255) NULL,
[STRING152] [nvarchar](255) NULL,
[STRING153] [nvarchar](255) NULL,
[STRING154] [nvarchar](255) NULL,
[STRING155] [nvarchar](255) NULL,
[STRING156] [nvarchar](255) NULL,
[STRING157] [nvarchar](255) NULL,
[STRING158] [nvarchar](255) NULL,
[STRING159] [nvarchar](255) NULL,
[STRING160] [nvarchar](255) NULL,
[STRING161] [nvarchar](255) NULL,
[STRING162] [nvarchar](255) NULL,
[STRING163] [nvarchar](255) NULL,
[STRING164] [nvarchar](255) NULL,
[STRING165] [nvarchar](255) NULL,
[STRING166] [nvarchar](255) NULL,
[STRING167] [nvarchar](255) NULL,
[STRING168] [nvarchar](255) NULL,
[STRING169] [nvarchar](255) NULL,
[STRING170] [nvarchar](255) NULL,
[STRING171] [nvarchar](255) NULL,
[STRING172] [nvarchar](255) NULL,
[STRING173] [nvarchar](255) NULL,
[STRING174] [nvarchar](255) NULL,
[STRING175] [nvarchar](255) NULL,
[STRING176] [nvarchar](255) NULL,
[STRING177] [nvarchar](255) NULL,
[STRING178] [nvarchar](255) NULL,
[STRING179] [nvarchar](255) NULL,
[STRING180] [nvarchar](255) NULL,
[STRING181] [nvarchar](255) NULL,
[STRING182] [nvarchar](255) NULL,
[STRING183] [nvarchar](255) NULL,
[STRING184] [nvarchar](255) NULL,
[STRING185] [nvarchar](255) NULL,
[STRING186] [nvarchar](255) NULL,
[STRING187] [nvarchar](255) NULL,
[STRING188] [nvarchar](255) NULL,
[STRING189] [nvarchar](255) NULL,
[STRING190] [nvarchar](255) NULL,
[STRING191] [nvarchar](255) NULL,
[STRING192] [nvarchar](255) NULL,
[STRING193] [nvarchar](255) NULL,
[STRING194] [nvarchar](255) NULL,
[STRING195] [nvarchar](255) NULL,
[STRING196] [nvarchar](255) NULL,
[STRING197] [nvarchar](255) NULL,
[STRING198] [nvarchar](255) NULL,
[STRING199] [nvarchar](255) NULL,
[STRING200] [nvarchar](255) NULL,
[STRING201] [nvarchar](255) NULL,
[STRING202] [nvarchar](255) NULL,
[STRING203] [nvarchar](255) NULL,
[STRING204] [nvarchar](255) NULL,
[STRING205] [nvarchar](255) NULL,
[STRING206] [nvarchar](255) NULL,
[STRING207] [nvarchar](255) NULL,
[STRING208] [nvarchar](255) NULL,
[STRING209] [nvarchar](255) NULL,
[STRING210] [nvarchar](255) NULL,
[STRING211] [nvarchar](255) NULL,
[STRING212] [nvarchar](255) NULL,
[STRING213] [nvarchar](255) NULL,
[STRING214] [nvarchar](255) NULL,
[STRING215] [nvarchar](255) NULL,
[STRING216] [nvarchar](255) NULL,
[STRING217] [nvarchar](255) NULL,
[STRING218] [nvarchar](255) NULL,
[STRING219] [nvarchar](255) NULL,
[STRING220] [nvarchar](255) NULL,
[STRING221] [nvarchar](255) NULL,
[STRING222] [nvarchar](255) NULL,
[STRING223] [nvarchar](255) NULL,
[STRING224] [nvarchar](255) NULL,
[STRING225] [nvarchar](255) NULL,
[STRING226] [nvarchar](255) NULL,
[STRING227] [nvarchar](255) NULL,
[STRING228] [nvarchar](255) NULL,
[STRING229] [nvarchar](255) NULL,
[STRING230] [nvarchar](255) NULL,
[STRING231] [nvarchar](255) NULL,
[STRING232] [nvarchar](255) NULL,
[STRING233] [nvarchar](255) NULL,
[STRING234] [nvarchar](255) NULL,
[STRING235] [nvarchar](255) NULL,
[STRING236] [nvarchar](255) NULL,
[STRING237] [nvarchar](255) NULL,
[STRING238] [nvarchar](255) NULL,
[STRING239] [nvarchar](255) NULL,
[STRING240] [nvarchar](255) NULL,
[STRING241] [nvarchar](255) NULL,
[STRING242] [nvarchar](255) NULL,
[STRING243] [nvarchar](255) NULL,
[STRING244] [nvarchar](255) NULL,
[STRING245] [nvarchar](255) NULL,
[STRING246] [nvarchar](255) NULL,
[NUM1] [decimal](28, 10) NULL,
[NUM2] [decimal](28, 10) NULL,
[NUM3] [decimal](28, 10) NULL,
[NUM4] [decimal](28, 10) NULL,
[NUM5] [decimal](28, 10) NULL,
[NUM6] [decimal](28, 10) NULL,
[NUM7] [decimal](28, 10) NULL,
[NUM8] [decimal](28, 10) NULL,
[NUM9] [decimal](28, 10) NULL,
[NUM10] [decimal](28, 10) NULL,
[NUM11] [decimal](28, 10) NULL,
[NUM12] [decimal](28, 10) NULL,
[NUM13] [decimal](28, 10) NULL,
[NUM14] [decimal](28, 10) NULL,
[NUM15] [decimal](28, 10) NULL,
[NUM16] [decimal](28, 10) NULL,
[NUM17] [decimal](28, 10) NULL,
[NUM18] [decimal](28, 10) NULL,
[NUM19] [decimal](28, 10) NULL,
[NUM20] [decimal](28, 10) NULL,
[NUM21] [decimal](28, 10) NULL,
[NUM22] [decimal](28, 10) NULL,
[NUM23] [decimal](28, 10) NULL,
[NUM24] [decimal](28, 10) NULL,
[NUM25] [decimal](28, 10) NULL,
[NUM26] [decimal](28, 10) NULL,
[NUM27] [decimal](28, 10) NULL,
[NUM28] [decimal](28, 10) NULL,
[NUM29] [decimal](28, 10) NULL,
[NUM30] [decimal](28, 10) NULL,
[NUM31] [decimal](28, 10) NULL,
[NUM32] [decimal](28, 10) NULL,
[NUM33] [decimal](28, 10) NULL,
[NUM34] [decimal](28, 10) NULL,
[NUM35] [decimal](28, 10) NULL,
[NUM36] [decimal](28, 10) NULL,
[NUM37] [decimal](28, 10) NULL,
[NUM38] [decimal](28, 10) NULL,
[NUM39] [decimal](28, 10) NULL,
[NUM40] [decimal](28, 10) NULL,
[NUM41] [decimal](28, 10) NULL,
[NUM42] [decimal](28, 10) NULL,
[NUM43] [decimal](28, 10) NULL,
[NUM44] [decimal](28, 10) NULL,
[NUM45] [decimal](28, 10) NULL,
[NUM46] [decimal](28, 10) NULL,
[NUM47] [decimal](28, 10) NULL,
[NUM48] [decimal](28, 10) NULL,
[NUM49] [decimal](28, 10) NULL,
[NUM50] [decimal](28, 10) NULL,
[DATE1] [datetime2](7) NULL,
[DATE2] [datetime2](7) NULL,
[DATE3] [datetime2](7) NULL,
[DATE4] [datetime2](7) NULL,
[DATE5] [datetime2](7) NULL,
[DATE6] [datetime2](7) NULL,
[DATE7] [datetime2](7) NULL,
[DATE8] [datetime2](7) NULL,
[DATE9] [datetime2](7) NULL,
[DATE10] [datetime2](7) NULL,
[DATE11] [datetime2](7) NULL,
[DATE12] [datetime2](7) NULL,
[DATE13] [datetime2](7) NULL,
[DATE14] [datetime2](7) NULL,
[DATE15] [datetime2](7) NULL,
[DATE16] [datetime2](7) NULL,
[DATE17] [datetime2](7) NULL,
[DATE18] [datetime2](7) NULL,
[DATE19] [datetime2](7) NULL,
[DATE20] [datetime2](7) NULL,
[DATE21] [datetime2](7) NULL,
[DATE22] [datetime2](7) NULL,
[DATE23] [datetime2](7) NULL,
[DATE24] [datetime2](7) NULL,
[DATE25] [datetime2](7) NULL,
[DATE26] [datetime2](7) NULL,
[DATE27] [datetime2](7) NULL,
[DATE28] [datetime2](7) NULL,
[DATE29] [datetime2](7) NULL,
[DATE30] [datetime2](7) NULL,
[DATE31] [datetime2](7) NULL,
[DATE32] [datetime2](7) NULL,
[DATE33] [datetime2](7) NULL,
[DATE34] [datetime2](7) NULL,
[DATE35] [datetime2](7) NULL,
[DATE36] [datetime2](7) NULL,
[DATE37] [datetime2](7) NULL,
[DATE38] [datetime2](7) NULL,
[DATE39] [datetime2](7) NULL,
[DATE40] [datetime2](7) NULL,
[DATE41] [datetime2](7) NULL,
[DATE42] [datetime2](7) NULL,
[DATE43] [datetime2](7) NULL,
[DATE44] [datetime2](7) NULL,
[DATE45] [datetime2](7) NULL,
[DATE46] [datetime2](7) NULL,
[DATE47] [datetime2](7) NULL,
[DATE48] [datetime2](7) NULL,
[DATE49] [datetime2](7) NULL,
[DATE50] [datetime2](7) NULL,
[BOOL1] [bit] NULL,
[BOOL2] [bit] NULL,
[BOOL3] [bit] NULL,
[BOOL4] [bit] NULL,
[BOOL5] [bit] NULL,
[BOOL6] [bit] NULL,
[BOOL7] [bit] NULL,
[BOOL8] [bit] NULL,
[BOOL9] [bit] NULL,
[BOOL10] [bit] NULL,
[BOOL11] [bit] NULL,
[BOOL12] [bit] NULL,
[BOOL13] [bit] NULL,
[BOOL14] [bit] NULL,
[BOOL15] [bit] NULL,
[BOOL16] [bit] NULL,
[BOOL17] [bit] NULL,
[BOOL18] [bit] NULL,
[BOOL19] [bit] NULL,
[BOOL20] [bit] NULL,
[BOOL21] [bit] NULL,
[BOOL22] [bit] NULL,
[BOOL23] [bit] NULL,
[BOOL24] [bit] NULL,
[BOOL25] [bit] NULL,
[BOOL26] [bit] NULL,
[BOOL27] [bit] NULL,
[BOOL28] [bit] NULL,
[BOOL29] [bit] NULL,
[BOOL30] [bit] NULL,
[BOOL31] [bit] NULL,
[BOOL32] [bit] NULL,
[BOOL33] [bit] NULL,
[BOOL34] [bit] NULL,
[BOOL35] [bit] NULL,
[BOOL36] [bit] NULL,
[BOOL37] [bit] NULL,
[BOOL38] [bit] NULL,
[BOOL39] [bit] NULL,
[BOOL40] [bit] NULL,
[BOOL41] [bit] NULL,
[BOOL42] [bit] NULL,
[BOOL43] [bit] NULL,
[BOOL44] [bit] NULL,
[BOOL45] [bit] NULL,
[BOOL46] [bit] NULL,
[BOOL47] [bit] NULL,
[BOOL48] [bit] NULL,
[BOOL49] [bit] NULL,
[BOOL50] [bit] NULL,
CONSTRAINT [PK_LabelValues] PRIMARY KEY NONCLUSTERED
(
[ContactID] ASC
)
) ON [FileGroup]
GO
January 8, 2013 at 8:02 am
Just shoot me!!! That is a nightmare!!!
There are a couple options as I see it. Either normalize that table into something that makes sense or suffer horrible performance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply