asked on

Amazon RDS Sub-queries excruciatingly slow

I have stored procedures that work perfectly fine on standard SQL Server instances. 2008, 12... etc

However on Amazon RDS they can take an excruciating amount of time to run.
Instead of the normal 2 seconds max... they take 13 minutes if you let them run

I have isolated the issue...and we have same indexes on the tables...

In standard SQL if I have a query with a join on a sub query like the one below... its fine
On RDS 13 minutes

UNLESS... in RDS I FIRST create a @table
Insert data from the same query
Then join on @table
If I do that... it is actually FASTER than standard SQL.

Any ideas on why this is?

 JOIN (   SELECT   MAX(a.IndividualDispositionID) AS MaxRowID ,
                                                   a.IndividualID
                                          FROM     IndividualDispositions a
                                          WHERE    DATEDIFF(DAY, @DateRangeStart, a.DateAdded) >= 0
                                                   AND DATEDIFF(DAY, a.DateAdded, @DateRangeEnd) >= 0
                                          GROUP BY IndividualID ) a ON c.IndividualDispositionID = a.MaxRowID
                        WHERE    c.IndividualExportsMasterID IN (   SELECT c.IndividualExportsMasterID
                                                                    FROM   dbo.IndividualExportsMaster c
                                                                    WHERE  c.SalesSiteID IN (   SELECT *
                                                                                                FROM   dbo.ParseList(@SalesSiteID, ',') ))

Open in new window

Valliappan AN

Possibly your RDS table had large number of records, and when you JOIN, it takes much time. Whereas, you filter out the table earlier, either to a temporary table or table variable, you deal with less number of records in JOIN, is my guess.

Regards.

Larry Brister

ASKER

Valliapan
Bot servers have a similar number fo records to query... @ 10K

Mark Wills

Smells like Parameter Sniffing.

By creating a variable within the proc, and loading into that, it overcomes the problem.

Although, I would have thought dbo.ParseList(@SalesSiteID, ',') might have still caused problems.

There are two declaratives used in the proc (assuming parameters)

You could also try declaring as local variables, setting those to the parameter values and then use them in the proc.

What datatype is @SalesSiteID - bet it is a long (n)varchar ?

Larry Brister

ASKER

@SalesSiteID is a comma separated varchar(1000) string

Parselist turns that into a list

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

MAkes sense... thanks