|
Group: StrataFrame Developers
Posts: 6.6K,
Visits: 6.9K
|
Yes, I live in execution plans and I also spend a vast amount of time creating the most optimized queries as possible. The other day we had a query that would have taken 60 minutes and by adding only a few keys, changing the logic, and using index hints, we got this same time down to 250 milliseconds. Regardless, that should give you a workaround. 
|
|
Group: StrataFrame Users
Posts: 193,
Visits: 9K
|
Trent, INCLUDing columns in index often improve performances, especially if you have complex views on tables with many columns (say more than 60 or 80 columns in 2 or more tables). You can see it in execution plans. I didn't think about post-deployment scripts as a workaround, but of course, it is probably the best way. Thanks for advice!
|
|
Group: StrataFrame Developers
Posts: 6.6K,
Visits: 6.9K
|
Wow, I have never created an index with that many columns!  I would have to actually test this to give you any conclusive results. But most likely, this is a limit of SMO. There is an easy enough workaround for something like this. You could just use a Post Deployment Script to create this index if the standard T-SQL code is allowing it. I will have to look into the other.
|
|
Group: StrataFrame Users
Posts: 193,
Visits: 9K
|
Hi, All is OK when I create an index within SSMS with this script CREATE NONCLUSTERED INDEX [IX_ligne_1_rpt_et_dev1] ON [dbo].[ligne_1]
(
[cde_cmd] ASC,
[no_ligne_1] ASC,
[cde_mat] ASC
)
INCLUDE ( [epaisseur],
[cal_surf],
[surf_lq],
[lq_m2],
[px2_m2],
[laq_l1],
[laq_l2],
[laq_f2],
[modew],
[mat_four],
[libelle3],
[longueur],
[largeur],
[raly],
[com_lg1],
[typ_laq],
[fl_bcl]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]but the same description in DDT raises an error when deployment (in log file
X 12/02/2010 16:19:50 -> Le index 'IX_ligne_1_rpt_et_dev1' sur la table 'dbo.ligne_1' a 20 noms de colonnes dans la liste de clés index. La limite maximale pour la liste des colonnes de clés d'index ou de statistiques est 16. Is that limit of 16 column names in an index definition a SF DDT limit, or a Microsoft SMO limit? We really need many indexes like this (with sometimes more than 40 columns as INCLUDE), and hoped that DDT could manage it. Thanks in advance
|