70 reputation
5
bio website
location
age
visits member for 1 year, 5 months
seen Mar 3 at 9:57

Mar
3
revised oracle crash with ORA-00942: table or view does not exist
short headline with description of cause
Mar
3
answered oracle crash with ORA-00942: table or view does not exist
Jan
28
revised Does Oracle ADO.NET varchar parameter size influence query plan reuse?
added 17 characters in body
Jan
28
revised Does Oracle ADO.NET varchar parameter size influence query plan reuse?
cursor sharing setting
Jan
28
asked Does Oracle ADO.NET varchar parameter size influence query plan reuse?
Jan
1
comment Is using multiple foreign keys separated by commas wrong, and if so, why?
Do you really think a string with comma separated child IDs is helpful? I mean, the application had to read first, then parse the IDs and query all the children, like select * from DealCategories where DealId in (1,2,3,4,...). You have more experience, regarding database design, than me, so maybe you have good reason in some cases for such "extreme tuning" in very specific cases. My only idea to justify this is a very high select load on Deal/DealCategory. This looks to me much like some outsource team without any DB design knowledge, beyond creating tables, created it.
Dec
31
comment Get all items from list table which don't have a successful log entry for 'sent' in log table
yes, list_id first, then action and failure_reason.
Dec
31
comment Should I add multiple secondary indexes to search
sorry, MichaelD, wrong advice. Different parameterization requires different query plans, a stored procedure will create only one for all (or be treated like a bloated Ad-Hoc query). If no parameter is submitted, it should not be in the query plan! These optional parameters with null checks must go away!
Dec
31
answered Is using multiple foreign keys separated by commas wrong, and if so, why?
Dec
28
comment Should I add multiple secondary indexes to search
@Kenneth Fisher: True if the main table is very broad, the Data columns and others are big, like nvarchar(4000/max) columns. Or if the index can restrict the search, such as an index on country and the names, or country, zip, names, with a restriction on the first.
Dec
28
comment Should I add multiple secondary indexes to search
@user636525 If you're coding in .NET, using ADO.NET, you will likely build them like this: var sql=new StringBuilder("SELECT * FROM Customer"); then, for the parameters to be filtered by, such as sql.Append("WHERE Zip=@paramzip"); (NOT: "WHERE Zip=" + myZip.ToString()!!!). Predefining all possible SQL is possible (if Country is mandatory, only 8), but maybe too much effort for little effect. You will need at least 4 indices (3 if country mandatory, each with Country), one for each searched column. I would at least add one more column to each: Country,Zip ; Country,FirstName etc
Dec
28
comment Should I add multiple secondary indexes to search
I think OPTION(RECOMPILE) causes treatment of the SQL within the procedure as an Ad-Hoc query, with recalculation of a query plan every time it is executed. Thus eliminating the main advantage of a stored procedure (if there was any in this case). Dynamically built SQL has a good chance of query plan reuse, when the SQL string is equal (including capitalization, spaces etc.) for the same search conditions. It has, of course, the risk of the optimizer sniffing for the wrong parameter values, resulting in non-optimal plans in most other cases.
Dec
28
revised Should I add multiple secondary indexes to search
deleted 5 characters in body
Dec
28
comment Should I add multiple secondary indexes to search
Index update times are mostly negligible, compared to workload caused by lack of proper indices. The above code is anyway not good for performance, as I wrote in the other answer, so fixing that, and adding more, correct indices is more important than to worry about index update times.
Dec
28
answered primary key: username+current time or serial
Dec
28
revised Should I add multiple secondary indexes to search
added 223 characters in body
Dec
28
answered Should I add multiple secondary indexes to search
Dec
28
answered Could not continue scan with NOLOCK due to data movement SQL Server 2008 R2
Dec
27
asked Clustered Index / Index Oriented Table performance in join, worse than nonclustered?
Dec
4
revised PreparedStatement caching size on Oracle and SQL Server
added 76 characters in body