speed up Parametrized Query
I use this code in vb6 and DAO:
Code:
...
DoEvents
Set QRY = DB.QueryDefs("AGG_ALBERO1")
QRY.Parameters("[DATA_OPE1]").Value = Format(DATA_OPE, "DD/MM/YYYY")
QRY.Execute
...
1) i can speed up that?
2) i can insert into execution QRY the param adExecuteNoRecords? (in my case the query dont retiurn recordset but only Update a table)
3) i need to execute the QRY with other sintiax?
Tks.
QUERY:
Code:
UPDATE CONTANTI INNER JOIN ANA_AG ON CONTANTI.SPORT = ANA_AG.AG SET CONTANTI.DT = [ANA_AG].[DT], CONTANTI.DESCR_DT = [ANA_AG].[DESCR_DT], CONTANTI.AREA = [ANA_AG].[AREA], CONTANTI.GRUPPO = [ANA_AG].[GRUPPO]
WHERE (((Len(Trim([CONTANTI].[DT])))=0) AND ((CONTANTI.COD) Not In ('GA','CA')) AND ((CONTANTI.DATA)=[DATA_OPE1])) OR (((Len(Trim([CONTANTI].[DT]))) Is Null))
Re: speed up Parametrized Query
I have no idea how to help because you are posting VB6 code and no query at all.
Either post this in vb6 thread if you want vb6 help or post the SQL query you need to speed up if you need sql help.
Re: speed up Parametrized Query
added original query in the first post.
Tks in other case.
Re: speed up Parametrized Query
Quote:
Originally Posted by
sapator
I have no idea how to help because you are posting VB6 code and no query at all.
Either post this in vb6 thread if you want vb6 help or post the SQL query you need to speed up if you need sql help.
added original query in the first post.
Tks in other case.
Re: speed up Parametrized Query
You could set up indexes on the apropriate columns in the query. E.g. anything that appears in the join or where clause so that's CONTANTI.COD, CONTANTI.DATA, CONTANTI.SPORT and ANA_AG.AG in your case. At a glance I'd suggest an index on CONTANTI.COD and CONTANTI.DATA to support the where clause and a separate index on CONTANTI.SPORT to support the join but that's only a best guess given the information here. You'd need to closely examine the execution plans to get to the best answer.
Also I think this part of the where clause:-
Code:
OR (((Len(Trim([CONTANTI].[DT]))) Is Null))
is pointless. A length can never be null as far as I'm aware.
Re: speed up Parametrized Query
Quote:
Originally Posted by
FunkyDexter
You could set up indexes on the apropriate columns in the query. E.g. anything that appears in the join or where clause so that's CONTANTI.COD, CONTANTI.DATA, CONTANTI.SPORT and ANA_AG.AG in your case. At a glance I'd suggest an index on CONTANTI.COD and CONTANTI.DATA to support the where clause and a separate index on CONTANTI.SPORT to support the join but that's only a best guess given the information here. You'd need to closely examine the execution plans to get to the best answer.
Also I think this part of the where clause:-
Code:
OR (((Len(Trim([CONTANTI].[DT]))) Is Null))
is pointless. A length can never be null as far as I'm aware.
That made me curious. Unless I'm missing something it can (MS SQL 2005).
Code:
create table #VBForums(MyNullColumn char(1))
insert into #VBForums(MyNullColumn) values(null)
set nocount on
select case
when len(MyNullColumn) is null then 'Length is null' else 'Length is not null' end as 'TestIt'
from #vbforums
-- or
select len(MyNullColumn) from #vbforums
drop table #vbforums
Re: speed up Parametrized Query
Quote:
Unless I'm missing something it can
Well I never. You live and learn:blush:
I think I'd probably test for that case by simply checking
Code:
MyNullColumn is null
I think that would be the same wouldn't it?
Or
in the case of the OP's query. The reason it first jumped out at me as an odd thing to do was that an index on that column wouldn't have helped speed up the query structured the way the OP had it.