Query Year in TeraData

Hi. You should NOT do it that way in MS SQL or Teradata. The reason why is quite simple:

a. for EVERY ROW you are running a function (this consumes cpu), and, MORE IMPORTANTLY
b. using a function on the column date DISABLES any INDEX in query optimization on the column
i.e. it makes your query SLOWER than it should be

there is a simple saying: "don't use functions on data in the where clause"

This is what you asked for:

Select *
from dbo.SomeTable
Where   EXTRACT (YEAR FROM Date_Field)   between 1993 and 1998

But in truth the faster way is this:

Select *
from dbo.SomeTable
Where  Date_Field >= '1993-01-01' and Date_Field  < '1999-01-01'

Also note, between is just not good for date ranges, always use >= with < instead
see Beware of Between

the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.

Itzik Ben-Gan

Well put!!! I realized that not to long ago had to remove the between and do >= and <.

As for the years. I understand that but this query is already very complex add the month date i thinks makes it more complex.

So here is what I am doing I will try explain best I can.

literally 10 Minutes ago I realized this has to be a 3 layer loop query

First Level easy 1-12 possible medical Diag codes on a professional claim
Second Level MemberID between 53,003,282,427 - 53,248,364,872(245 Million difference) going thru this in chucks of 5 Million Appox 48 iterations
Third Level Years 30 years doing in chucks on 3 so 10 iterations(This is the part relevant to the original question)

that's 480 iterations by 12 so => 5760 Iterations

Why I am doing it this way its because data in 2 sources and I have to join 9 billion records to 20,000 records
9 billion are in TeraData and 20000 in MS SQL

So the query changes dynamically the MemberIDs and years and processes.

If you can think of a better way to do this I am all ears

Also the limitation on the box does not allow me to process more the 75 Million records at a time due to resources on machine. That why I have broke this out in such a way.

With those row numbers EVEN MORE REASON to AVOID functions on the data!!

The length of SQL is not a solid measure of its efficiency; frequently "sargable" predicates are way longer than common alternatives,  but sargable predicates perform better.

I don't think I can offer much on your overall need, that would require much more knowledge of the requirements and the data. If you include your existing query I might be able to make some suggestions.

good luck, sounds like a decent challenge.

My query. For security purposes I removed the columns being returned by query. This is a dynamic query.

The bold values are what changes in the iterations

SELECT Count(1)
FROM PHYSICIAN_CLAIM
Where HEADER_DIAGNOSIS_1_CODE not in  ('25000','25001','25002','25003','25010','25011','25012','25013','25020','25021','25022','25023','25030','25031','25032','25033','25040','25040','25041','25042','25043','25050','25051','25052','25052','25053','25060','25061','25062','25063','25070','25071','25072','25073','25080','25080','25081','25082','25083','25090','25091','25092','25093')
and HEADER_DIAGNOSIS_1_CODE is NOT NULL
and MEMBER_SYSTEM_ID >= 53003282427
and MEMBER_SYSTEM_ID < 53008282427
and EXTRACT(YEAR FROM SERVICE_FROM_DATE) >= 2014
and EXTRACT(YEAR FROM SERVICE_FROM_DATE) < 2017

The 2014 is not static it changes.

The concatenation is done in SSIS will I make these changes. Makes sense.

Thank you! This query may run for a few days I have no idea how many hours you just saved me looking at 9 billion rows 5760 times.

oh that sounds wrong and painful

Thank you!

A pleasure. Cheers.