For Microsoft SQL Server 2017 (RTM-CU20) on Azure running an ERP system (so changing the code here is not an option).
I have a table with over 42 million rows. It's a machine operating stats tracking table. Users enter operating statistics by equipment, date, shift and type of stat (Operating hours, Tons, whatever).
The table has a primary key structure like this (please no comments about nchar instead of nvarchar or storing dates as strings, that's how the ERP vendor provides it and that's just what I have to work with):
equip nchar(12) This is a 12 character equipment number key_type nchar(1) One of two values E for equipment or G - always E here shift_seq_no nchar(2) Sequence of shifts (usually 00 or 01) stat_date nchar(8) Date, YYYYMMDD stat_type nchar(2) A code for stat type, OH here (operating hours) trc_seq_no nchar(3) A sequence number for when there's more than one entry per shift
It appears that the calling code (java based web app making a JDBC call to the database with JBOSS in the middle of it all) queries a list of equipment scheduled for preventative maintenance in a given date range and goes looking for the most recent stat for each piece of equipment, since the row will contain the cumulative stat value for the equipment.
It then puts together a very long SQL query that effectively does this (replace x with appropriate variables:
select * from table where (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x) or (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x) or (equip = x and key_type = x and shift_seq_no = x and stat_date = x and stat_type = x and trc_seq_no = x) or ( and on and on)
There are 73 repeats of the 'OR' in total
I generated two queries based upon what I can see is running.
One where I just have a straight up set of 73 sets of literals (equip = '000001234567' and col = 'value' and etc) - this runs instantly. The actual execution plan shows a Clustered Index Seek (Clustered) in a Nested Loop with a Constant Scan showing 73 executions of 1 row read and a run time of less than 1 second
I then took the same query and declared 428 variables and plugged them into the query in place of the 428 literals instead. The actual execution plan shows a Clustered Index Scan (Clustered) in a single step (Execution plan says SELECT -> Filter -> Clustered Index Scan). The hint shows all 42,271,100 rows read with a run time of 5 minutes and 34 seconds when run in SSMS. This is a web based application and the debug tracing through the online app comes in at over 8 minutes. You can imagine that just isn't deemed acceptable for an online screen.
Now that the long story is complete, does anyone have any recommendations of SQL Server tuning that might be able to be done in order to make this query run via variables the same way it runs with literals since I can't touch the code.
I think I'll be sending this back to the vendor for analysis as well, but I was hoping that someone here in the brain trust might have an idea for a quicker solution (since my Google Fu has failed me at the moment).
I've seen several articles about this happening when querying a view and the general solution seems to be "add a WITH (RECOMPILE) hint" but I'm not using a view here, I'm querying a table directly.
Thanks in advance for any help here. I'm primarily an Oracle guy and know enough about SQL Server to be dangerous.