This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I've run into an issue with SQL Server where a query is using the incorrect index, due to this the query is taking over 1 minute to complete. It is fixable by giving SQLServer a hint on which index to use in the query. The query that's slow (1 minute 9 seconds): Normal 0 21 false false false NL X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} select min(id)from ticks where Time >GETDATE()-10 The query that's fast (<2 seconds): Normal 0 21 false false false NL X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} select min(id)from ticks with (index(IX_Time))where Time >GETDATE()-10 Is it possible to tell Lightspeed which index to use for a specific query? If it's currently not possible, will it be possible in the (near) future? I have no clue why SQL Server thinks its a good idea to use the primary-key index instead of the time index on this query. Regards, Jerremy |
|
|
(sorry about the great office-tags in there, but can't seem to edit my post to fix it) |
|
|
We don't have this facility but it sounds like it could be useful to add -- but I can't make any promises! In the meantime, I would suggest wrapping up the fast query in a stored procedure and calling it using IUnitOfWork.Calculate (the designer can also create a wrapper method on your strong-typed unit of work, e.g. MyUnitOfWork.GetFirstRecentTick()). |
|
|
I would suggest to implement this along those lines; uow.Ticks.WithIndex(Index_Name).Where().First() Both Oracle and SQLServer support a hint to use a specific index. Maybe you can keep this generic in that way by having mindscape warp this into the generated sqlquery in the right spot depending on what database is used. |
|
|
Hi chaps, There will be a first cut of this in the next nightly build, available for download from about 1200 GMT. In LINQ, you will be able to specify indexes using the new WithIndexHint method, which is basically the same as IvoTops' suggestion, e.g. uow.Ticks.WithIndexHint("IX_Whatever")... In the core API, you will be able to use the new Query.Hints object e.g. myQuery.Hints.Indexes.Add("IX_Whatever"). You should treat this feature as beta and please let us know about any bugs or quirks you run into. In particular, we have had some difficulty testing the feature on Oracle -- we believe it is working but we haven't been able to verify that Oracle is picking up the hint correctly. We would welcome any feedback from customers who are more familiar with Oracle performance tuning than we are! Index hints are currently supported on Oracle and SQL Server. On other databases, they will be silently ignored -- we take the attitude that they are 'hints' rather than guarantees, but we are open to feedback on this. Thanks for suggesting this feature! |
|
|
Wow, that's fast, Thanx. Will be testing SQLServer support today or tomorrow. >>we believe it is working but we haven't been able to verify that Oracle is picking up the hint correctly. With Oracle hints are specified within comments which makes them bothersome. Any typo will make Oracle ignore your hint without any kind of warning. The way to check if your hint is being used is by examining the execution plan. Oracle docs will tell you a hint is a hint and can be ignored by their optimizer, but in my experience (12 years on and off oracle database programming, upto 4 years ago) hints were never ignored. But I also feel I need them more with SQLServer because the queries I now require a hint for are so simple it is a disgrace for SQLServer that they are not correctly optimized imho. Although I used to *** a lot about the Oracle Optimizer, it never ever faltered with queries so simple. As a bonus feature you might check whether the specified index exists in the database. With oracle you can look this up in the metatable ALL_OBJECTS WHERE OBJECT_NAME ="IX_Whatever" AND OBJECT_TYPE="INDEX". I think I would like my query to fail in an error if a specify an index and it is no longer there so the query will take unduly long. But that might be a matter of taste.
|
|
|
Is working like a charm on SQLServer, thanx! |
|