Sql Server Date processing overhead / best practices

[Vis1/0N]

Hi, I am querying a SQL server database with over 500 000 records every 2 to 5 minutes and the query i plan to execute is

 "SELECT MAX(actual_date) AS Max_unit_id FROM dbo.trackingTable Where (tracking_area_Id=12) and ( product_id in " & Res & ")"

I am using the IN statement in the query to avoid a join and Res is either (5,6,11) or (1,2,3,4,7,8,9,10). How expensive is this to the server? Should i break it down and use a complex and statement OR for matching Product_ids?

How expensive is the overhead for getting max(actual_date) ? I need to get the date and time of the last unit tracked in the section for the product lines A (5,6,11) and B (1,2,3,4,7,8,9,10) and I want to avoid overhead on the server - which i am only a user and can't benchmark.

 

[*SynergyX*]

i think you may find the the execution plan for both queries, IN and Join will be the same.

 

[LPCPT]

CREATE TABLE [dbo].[trackingTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[tracking_area] [int] NOT NULL,
[product_id] [smallint] NOT NULL,
[actual_date] [date] NOT NULL,
CONSTRAINT [PK_trackingTable] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--Test area

truncate table trackingTable
go

Insert trackingTable(tracking_area, product_id, actual_date)
values
--Line A
(12, 5, '2016-01-01'),
(12, 6,'2016-10-01'),
(12, 11,'2016-10-31'),

--Line B
(12, 1,'2016-01-01'),
(12, 3,'2016-03-01'),
(12, 7,'2016-10-11'),
(12, 9,'2016-09-01'),
(12, 10,'2016-10-01')

GO 2000 --generate test data about 16K records

select Line, max(Max_unit_id) as Max_unit_id
from(
SELECT Line =
case when product_id in (5,6,11) then 'A'
when product_id in (1,2,3,4,7,8,9,10) then 'B'
else 'Z' end,
MAX(actual_date) AS Max_unit_id FROM dbo.trackingTable
Where (tracking_area=12)
group by product_id) as T
where Line in ('A', 'B')
Group By Line

 

[LPCPT]

You might want to add an index to that table.


CREATE INDEX [IX_trackingtable_trackingArea_Incl_ActualDate] ON [dbo].[trackingTable]
([tracking_area] ASC)
INCLUDE ([actual_date])
GO

--Edit
This one is better
CREATE NONCLUSTERED INDEX [IX_trackingtable_area_prodid]
ON [dbo].[trackingTable] ([tracking_area],[product_id])
INCLUDE ([actual_date])


And new query:
select Line, max(Max_unit_id) as Max_unit_id
from(
SELECT Line =
case when product_id in (5,6,11) then 'A'
when product_id in (1,2,3,4,7,8,9,10) then 'B'
else 'Z' end,
MAX(actual_date) AS Max_unit_id FROM dbo.trackingTable
Where (tracking_area=12) and product_id >= 1 and product_id <= 10
group by product_id) as T
where Line in ('A', 'B')
Group By Line

 

[Necropolis]

What are the specs on the server?

500,000 records really isn't that much at all - and even entry level hardware will run that query without breaking a sweat.

 

[Vis1/0N]

What are the specs on the server?

500,000 records really isn't that much at all - and even entry level hardware will run that query without breaking a sweat.

Quick answer ... have no idea on the server. But I do know that the previous queries (on another server) using datetime were changed. They added a new column ProdDate which was a flat text value. And the times were converted into flat hour and minute columns. They removed some screens and the queries and I was told it was performance related as they found that it was really bogging down the server. This was a different department and likely different database. Should I basically be unconcerned in querying a maximum date value ?

I can't update to the server, I am restricted tp read data only. On some occasions [some unrelated test installation] I have seen process sqlservr.exe consume memory without releasing - maybe it is system managed and I need not be bothered. I am not a db admin.

 

[Mr.Jax]

The Index LPCPT recommended is what you need, nothing else. The "INCLUDE" portion is what makes it good.

Your original query can stay as is. The execution plan will probably reveal an index seek or index scan, SQL server won't even touch the underlying table.

Yes, you should be unconcerned querying a max date value and just be concerned on how you retrieve the data the query will process.

 

[skimread]

first check if there are indexes on tracking_area_Id and product_id