1

First off, admittedly, I am no DBA...so my SQL-fu is weak.

I was recently working on a project that had a pretty hefty report that did 10 inner joins. When ran against Prod data (SQL Server 2005) using the SQL Studio Management client, the query wasn't a barn-burner, but it returned in just under 20sec. However, when ran through Spring, 31min.

So, we got our DBA ninja on it, and he pointed out that the query plan would be different because the JDBC method would use a prepared statement, passing in the variables as parameters, whereas in the client those were hard-coded. So, he re-worked the query.

The resulting query now sets some declare variables up top, then uses those to create a local temp table, then uses the local temp table as part of the ultimate report query. He said we should be able to send all this as part of the same query string (compound query????). It looks something like this (obfuscated to protect the innocent):

declare @startdate DateTime
declare @enddate DateTime

set @startdate = DATEADD (dd, 0, DATEDIFF (dd, 0, '2013-03-01 00:00:00.000'))
set @enddate = DATEADD (dd, 1, DATEDIFF (dd, 0, '2013-08-08 00:00:00.000'))

CREATE TABLE #LATEST_BLAH_ACTION
(
    FK_Blah_Timestamp DATETIME, 
    FK_Blah_Id VARCHAR(10), 
    Blah_Other_Thing VARCHAR(10),
    [Latest Updated Date/Time] DATETIME  
)
INSERT INTO #LATEST_BLAH_ACTION
SELECT FK_Blah_Timestamp, FK_Blah_Id, Blah_Other_Thing,  
  MAX(Blah_Other_Timestamp) AS [Latest Updated Date/Time] 
FROM BlahTable                        
WHERE Blah_Another_Thing = 'Some value' AND 
  Blah_Other_Timestamp BETWEEN @startdate and @enddate
GROUP BY FK_Blah_Timestamp, FK_Blah_Id, Blah_Other_Thing

SELECT
   -- Bunch of fields
  From LATEST_BLAH_ACTION
  -- Bunch of crazy Inner Joins and such

However it's not working. If I run this out of SQL Management Studio, I get back results. If I run it out of the Java code using Spring's SimpleJdbcTemplate, no error but no results.

Is it possible to run a (compound???) query like this using Spring, and if so, how? Do I have to do them individually, but as part of a transaction? Maybe use something other than SimpleJdbcTemplate?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
CodeChimp
  • 8,016
  • 5
  • 41
  • 79
  • 2
    What if you put that logic into a stored procedure, and execute the stored procedure from Spring? – Aaron Bertrand Aug 09 '13 at 20:40
  • I was trying to avoid stored procedures, as the DBAs here frown on them. But I guess if it's the only option, then it's the only option. – CodeChimp Aug 12 '13 at 10:46
  • usually it's devs who frown on stored procedures. What do your DBAs have against them? – Aaron Bertrand Aug 12 '13 at 11:40
  • Not sure. They do lots of strange things, like creating a view for every table. I just remember mentioning a stored procedure once to one of the other devs, and was told it was not something the DBAs liked to do. – CodeChimp Aug 12 '13 at 11:53
  • On a whim, I tried adding ';' to the end of each statement, and it's working now. However, I may have to re-evaluate this with the DBA, as all the reading I have done seems to indicate that, although some DBs support it, doing compound statements in one call makes the SQL non-portable. – CodeChimp Aug 12 '13 at 13:13
  • 1
    Is portability really a high priority here? How many other platforms are going to support this form of variable declaration, #temp tables, datetime, etc.? I think statement terminators should be among the least of your worries if you expect this code to work on Oracle, DB2, MySQL, etc. – Aaron Bertrand Aug 12 '13 at 13:16
  • https://stackoverflow.com/questions/4728495/temporary-tables-using-jdbc-with-null-resultset – Mitendra Nov 24 '17 at 09:11

0 Answers0