April 1, 2009 at 11:31 am
Hi guys, im new to the world of the sql, and i have trying to put an index into a view on 3 fields of the view, but when the index is going to be created it pops this error Msg:
Spanish SQL 😛
"Msg 1935, Level 16, State 1, Line 1
No se pudo crear el índice. Se creó el objeto 'IV00101' con las siguientes opciones SET desactivadas: 'ANSI_NULLS'."
My poor translation
"Msg 1935, Level 16, State 1, Line 1
Index cannot be created.The object 'IV00101' was created with the following options SET deactivated:
'ANSI_NULLS' "
The error looks obvious, but those tables are from an ERP and i cant change em.
Somebody know a way to deal with this, without any change to the tables?
The view has a lot of records, and i need to compare to those 3 fields that arn't keys in a lot of selects, so it takes some good time ruunning the procedures.
Any help would be uberly appreciated =)
Heres my code.
Please dont tell me how bad my code is XD im still learning :P, it works, but i know its not the best way to do it, feel free to give any recomendations =)
Thanks in advance 😀
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'VW_DET_ALM_POR_TIENDA') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW VW_DET_ALM_POR_TIENDA
GO
create view VW_DET_ALM_POR_TIENDA WITH SCHEMABINDING
as
select art.ITEMNMBR,art.USCATVLS_2,art.USCATVLS_3,art.USCATVLS_4,
trf.DOCNUMBR,trf.DOCDATE,trf.HSTMODUL,trf.EXTDCOST,trf.TRXLOCTN,
cat.USCATVAL,cat.USCATNUM, cat.Image_Url,
flt.POPRCTNM,flt.Landed_Cost_Id,flt.Orig_TotalLandedCostAmt
from dbo.iv00101 art right join dbo.iv40600 cat on art.USCATVLS_3 = cat.USCATVAL
right join dbo.iv30300 trf on trf.ITEMNMBR = art.ITEMNMBR
left join dbo.POP30700 flt on flt.POPRCTNM = trf.DOCNUMBR
GO
CREATE UNIQUE CLUSTERED INDEX IND_VW_DET_ALM_POR_TIENDA ON VW_DET_ALM_POR_TIENDA
(USCATVLS_2,USCATVLS_3,USCATVLS_4)
April 1, 2009 at 11:44 am
You won't be able to create an indexed view on that table. However, if you can index the columns you need on the tables, instead of the view, that should do what you need. Is that an option for you?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2009 at 11:50 am
GSquared (4/1/2009)
You won't be able to create an indexed view on that table. However, if you can index the columns you need on the tables, instead of the view, that should do what you need. Is that an option for you?
Let me see if i got it,
Your suggestion is to index the 3 fields directly in the source table of the view, so when i create the view, there are indexes already?
did i got right?
April 1, 2009 at 11:53 am
If you want to create an index on a view it needs to be schema bound.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 1, 2009 at 12:11 pm
Txs for the replies guys, but guess ill need to looks for another way, since i found this:
* You can not create an index on a view with outer joins used in it, even if you use schema binding
* You can not use '*' in the select statement of a view when it is schema bound. In such case you will clearly get error message of level 15 as "Syntax '*' is not allowed in schema-bound objects.".
* You will not be able to create clustered index on a view if the view references any nondeterministic functions.
* You can not use aggregate functions when using schema binding.
* You can not migrate the base table of a schema bound view.
XD
Imma try another way to create that view and see if i can get the querys to be a lil faster.
thanks 😀
April 1, 2009 at 12:20 pm
Did you try the solution GSquared suggested?
CREATE INDEX IX_iv00101_USCATVLS_2_3_4 ON iv00101 (USCATVLS_2,USCATVLS_3,USCATVLS_4)
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 1, 2009 at 12:29 pm
Yeah, i did it , and it reduced the execution time like 6 of 30 seconds, and thats cool.
And since the ERP tables keep growing like roaches, i think those index will be helping a lot on later days.
thanks guys =D
April 1, 2009 at 2:25 pm
If you post the execution plan of the query that's taking so long, we might be able to point out other things that could be done to improve it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2009 at 3:34 pm
For sure.
the querys i use r those
SET @INV_INICIAL = (select ISNULL(sum(EXTDCOST),0) FROM VW_DET_ALM_POR_TIENDA
WHERE month(docdate)= @invInicialMES
AND year(docdate)=@invInicialANIO
AND USCATVLS_3 = @DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)
SET @COMPRAS = (SELECT ISNULL(sum(EXTDCOST),0) FROM VW_DET_ALM_POR_TIENDA
WHERE DOCNUMBR like '%RB%'
AND month(docdate)= @MES --DEL MES DE REPORTE
AND year(docdate)=@ANIO --DEL AÑO DEL REPORTE
AND USCATVLS_3 = @DEPARTAMENTO -- QUE SEAN DE ESE DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3 -- cOMPLEMENTO DE INDENTIFICADOR DE DEPARTAMENTO
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)-- DE LA TIENDA REQUERIDA
SET @GASTOS_DE_COMPRA = (SELECT ISNULL(sum(Orig_TotalLandedCostAmt),0) FROM VW_DET_ALM_POR_TIENDA
WHERE Landed_Cost_Id like '%FLET%'
AND month(docdate)= @MES --DEL MES DE REPORTE
AND year(docdate)=@ANIO --DEL AÑO DEL REPORTE
AND USCATVLS_3 = @DEPARTAMENTO -- QUE SEAN DE ESE DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3 -- cOMPLEMENTO DE INDENTIFICADOR DE DEPARTAMENTO
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)-- DE LA TIENDA REQUERIDA
SET @DEVOL_COMPRAS = 0 --PENDIENTE PENDIENTE PENDIENTE PENDIENTE
SET @DIF_COMPRAS = 0 -- PENDIENTE PENDIENTE PENDIENTE PENDIENTE
SET @COMPRAS_NETAS = @COMPRAS - @GASTOS_DE_COMPRA - @DEVOL_COMPRAS
SET @TRANSF_ENTRADA = (SELECT COUNT (*) FROM VW_DET_ALM_POR_TIENDA
WHERE EXTDCOST > 0 --TRANFERENCIAS POSITIVAS
AND month(docdate)= @MES --DEL MES DE REPORTE
AND year(docdate)=@ANIO --DEL AÑO DEL REPORTE
AND USCATVLS_3 = @DEPARTAMENTO -- QUE SEAN DE ESE DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3 -- cOMPLEMENTO DE INDENTIFICADOR DE DEPARTAMENTO
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)-- DE LA TIENDA REQUERIDA
SET @TRANSF_SALIDA = (SELECT COUNT (*) FROM VW_DET_ALM_POR_TIENDA
WHERE EXTDCOST < 0 --TRANFERENCIAS POSITIVAS
AND month(docdate)= @MES --DEL MES DE REPORTE
AND year(docdate)=@ANIO --DEL AÑO DEL REPORTE
AND USCATVLS_3 = @DEPARTAMENTO -- QUE SEAN DE ESE DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3 -- cOMPLEMENTO DE INDENTIFICADOR DE DEPARTAMENTO
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)-- DE LA TIENDA REQUERIDA
then i use those values for simple additions and substractions then insert the values into a temp table,
but those querys are made within a fetch, thats why i do those selects a lot of times.
April 2, 2009 at 3:34 am
Hi
That's not the execution plan but the executing statement ;-).
In Management Studio open your query, then go to menu -> Query -> Include Actual Execution Plan.
Then execute your query. When it's finished you will get an additional tab in results with title "Execution Plan". Right click into its content and select "Save Execution Plan as...".
Include this into a ZIP file and attach it to a new post.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 2, 2009 at 3:59 am
We must wait to review your execution plan first, but probably the problem in the access could be fixed with an index over the fields:
USCATVLS_3 -- Departamento
USCATVLS_2 -- Division
TRXLOCTN -- Tienda
docdate -- Fecha
And you must try to avoid the use of functions over the fields in the where clause.
For example, instead of use:
month(docdate) and year(docdate)
use a function to obtain the first and last date to this year and month and make something like this:
@ini_date = '01' of month and year
@last_date = '01' of next month
docdate >= @ini_date and docdate < @last_date
with this type of sentence you can get benefit for the definition of an index over the date field.
April 2, 2009 at 9:49 am
:w00t: i didnt know about that feature XD,
im ataching the plan as u requested.
txs a lot for ur time guys =)
April 3, 2009 at 7:32 am
You have to save each section of the execution plan. All you got in the zip file was the first piece of the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2009 at 9:58 am
Aww im sorry i didnt checked the file i saved, my mistake,
now i send the plan of the whole store procedure that i execute, the final result is a recordset that i need
hope this time im not making it wrong :crazy:
Again, txs a lot for ur time guys 😀
April 3, 2009 at 12:19 pm
Can you post the code for that proc? The create script would be best.
There are parts on the execution plan that lead me to believe it would be possible to improve the proc, probably significantly.
It would also be helpful if you include the create scripts for the tables, etc., that the proc uses. But at least the proc, please.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply