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))

---

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.

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.

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.

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

---

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

Code:

---

[CONTANTI].[DT]

---

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.