great, that works! code is a little buliker but does the job nicely! thanks

no, absolutely for test purposes only. OPTION (MERGE JOIN) doesnt make any difference.

here are the queries:

SELECT m.LastName, m.FirstName, m.Region_No

FROM dbo.Member AS m

WHERE m.FirstName = 'Jim'

OR m.LastName = 'Smith'

go

SELECT m.LastName, m.FirstName,...

Grant Fritchey (1/13/2009)

---

Personally, I've never found the DTA to be accurate enough on the really complex queries that I was working on. If that's the case here, you might want...

MarkusB (1/13/2009)

---

No, I don't know how exactly DTA determines which indexes it recommends and I only use it occassionally.

But you say you wouldn't come up with the recommended index...

GilaMonster (1/12/2009)

---

Probably it thinks that the index that has the include columns will...

excellent! Thanks a mil Gail! Its all starting to make sense now. annoying that information gained on a course that was a bit expensive was leading me on the wrong...

im thinking the optimizer will pick the (string, dt, id) index as it has less rows to return with the string, max of 100, rather than 5000 or 10000 rows...

GilaMonster (1/7/2009)

---

The query optimiser will use the statistics on the two indexes to work out which option will reduce the rows to be considered the most, ie whether m.Region_No >...

one more question. take the first index:

create index on table1 (col1)

include(col2, col3)

in this index, the index key is Col1 and in the leaf level, the data from col2 and col3...

yes, this is really great stuff. thanks a mil. Im just getting into optimization, outside of the the dynamic views and functions which are not all that reliable. the show_statistics...

GilaMonster (1/7/2009)

---

Since all of the filters are inequalities, SQL can only seek on one of them. The other two have to be applied as a second step after the index...

when i run dbcc show_statistics for each statistic, what info do you require? Ive never read statistics before so not sure what im looking at/for.

thanks folks. as always, the solution is staring me in the face yet i miss it. much appriciated.

so, what is your process?

Do you store the files as log files, and then import them into a database via a job containing a dts/SSIS package?

Can perfmon log the data...

bummer. it would be real useful to be able to know how many transactions are being committed, as only committed transactions will be replicated as far as i know...