Solved

SQL Server String Performance

Posted on 2014-09-15
11
301 Views
Last Modified: 2014-09-17
Hello, I have the following SQL Server string:

SELECT DISTINCT 
                      TOP (40) dbo.DDB.brandname, SQRT(POWER(dbo.DDB.latitude - 29.935313, 2) 
                      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 AS Expr1, dbo.DDB.locationName, 
                      dbo.DDB.streetLine1, dbo.DDB.city, dbo.DDB.state, dbo.DDB.postalCode, 
                      dbo.DDB.brandLogoName, dbo.DDB.locationIdentifier, dbo.DDB.offerIdentifier, 
                      dbo.DDB.description, dbo.DDB.categoryIdentifier, dbo.DDB.source
FROM         dbo.DDB INNER JOIN
                      dbo.CATEGORY_LINKS ON dbo.DDB.categoryIdentifier = dbo.CATEGORY_LINKS.LinkedCategory
WHERE     (dbo.DDB.maxprints > dbo.DDB.prints) AND (SQRT(POWER(dbo.DDB.latitude - 29.935313, 2) 
                      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 BETWEEN 0 AND 20)

Open in new window


This Works perfect, but when I add the line ORDER BY Expr1 at the end, the performance decreases a lot, actually, the CPU usage jumps to 30%.

Is there a way to sort the results without losing performance?

Thanks
0
Comment
Question by:SimpleDude
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40324081
Could you persist this data as a computed column in the DDB database?

http://msdn.microsoft.com/en-us/library/ms188300(v=sql.105).aspx
0
 

Author Comment

by:SimpleDude
ID: 40324097
Hi Russell,

I cannot set a computed column cause 0.014457  and -90.068825 are variables that change almost on every user click.

Thanks!
0
 
LVL 1

Expert Comment

by:Sleepydog
ID: 40324177
What if you persist this data into a #tempTable, then use your order by in the temp table? Not sure if this is the correct syntax but here goes...

 SELECT DISTINCT 
                      TOP (40) dbo.DDB.brandname, SQRT(POWER(dbo.DDB.latitude - 29.935313, 2) 
                      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 AS Expr1, dbo.DDB.locationName, 
                      dbo.DDB.streetLine1, dbo.DDB.city, dbo.DDB.state, dbo.DDB.postalCode, 
                      dbo.DDB.brandLogoName, dbo.DDB.locationIdentifier, dbo.DDB.offerIdentifier, 
                      dbo.DDB.description, dbo.DDB.categoryIdentifier, dbo.DDB.source
INTO #TempTable
FROM         dbo.DDB INNER JOIN
                      dbo.CATEGORY_LINKS ON dbo.DDB.categoryIdentifier = dbo.CATEGORY_LINKS.LinkedCategory
WHERE     (dbo.DDB.maxprints > dbo.DDB.prints) AND (SQRT(POWER(dbo.DDB.latitude - 29.935313, 2) 
                      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 BETWEEN 0 AND 20)

SELECT * FROM #TempTable
ORDER BY Expr1
 

Open in new window

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:SimpleDude
ID: 40324223
Hi Sleepy, thanks for the reply. I tried that solution before but the result is sort of the same.
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40324342
You can try creating a new nonclustered index on ([DDB_ID], [Latitude], [Longitude]), and then create a function that uses the new index and can be called in your query:
CREATE FUNCTION fGetLatLon
(
	@Lat FLOAT,
	@Lon FLOAT,
	@Factor FLOAT,
	@DDBID INT
)
RETURNS FLOAT
AS
BEGIN

	DECLARE @LatLon FLOAT

	SELECT @LatLon = SQRT(POWER(dbo.DDB.latitude - @Lat, 2) + POWER(dbo.DDB.longitude - (@Lon), 2)) / @Factor
	FROM dbo.DDB WITH (INDEX (NewIndexName))
	WHERE ID = @DDBID

	RETURN COALESCE(@LatLon, 0)

END
GO
-- Then your query looks like this:
DECLARE @Lat FLOAT = 29.935313,
	@Lon FLOAT = -90.068825,
	@Factor FLOAT = 0.014457

SELECT DISTINCT 
    TOP (40) dbo.DDB.brandname, 
	fGetLatLon(@Lat, @Lon, @Factor, dbo.DDB.ID) AS Expr1,
	dbo.DDB.locationName, 
    dbo.DDB.streetLine1, dbo.DDB.city, dbo.DDB.state, dbo.DDB.postalCode, 
    dbo.DDB.brandLogoName, dbo.DDB.locationIdentifier, dbo.DDB.offerIdentifier, 
    dbo.DDB.description, dbo.DDB.categoryIdentifier, dbo.DDB.source
FROM dbo.DDB INNER JOIN
		dbo.CATEGORY_LINKS ON dbo.DDB.categoryIdentifier = dbo.CATEGORY_LINKS.LinkedCategory
WHERE (dbo.DDB.maxprints > dbo.DDB.prints) 
	AND fGetLatLon(@Lat, @Lon, @Factor, dbo.DDB.ID) BETWEEN 0 AND 20

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40324615
What happens if you remove DISTINCT and the unused table?
SELECT TOP (40)
      dbo.DDB.brandname
    , SQRT(POWER(dbo.DDB.latitude - 29.935313, 2)
      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 AS Expr1
    , dbo.DDB.locationName
    , dbo.DDB.streetLine1
    , dbo.DDB.city
    , dbo.DDB.state
    , dbo.DDB.postalCode
    , dbo.DDB.brandLogoName
    , dbo.DDB.locationIdentifier
    , dbo.DDB.offerIdentifier
    , dbo.DDB.description
    , dbo.DDB.categoryIdentifier
    , dbo.DDB.source
FROM dbo.DDB
WHERE (dbo.DDB.maxprints > dbo.DDB.prints)
      AND (SQRT(POWER(dbo.DDB.latitude - 29.935313, 2)
      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 BETWEEN 0 AND 20)
ORDER BY
      Expr1

Open in new window

0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40324769
Use the query to create a view and select from the view:
CREATE VIEW MyViewName
AS
SELECT DISTINCT 
                      TOP (40) dbo.DDB.brandname, SQRT(POWER(dbo.DDB.latitude - 29.935313, 2) 
                      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 AS Expr1, dbo.DDB.locationName, 
                      dbo.DDB.streetLine1, dbo.DDB.city, dbo.DDB.state, dbo.DDB.postalCode, 
                      dbo.DDB.brandLogoName, dbo.DDB.locationIdentifier, dbo.DDB.offerIdentifier, 
                      dbo.DDB.description, dbo.DDB.categoryIdentifier, dbo.DDB.source
FROM         dbo.DDB INNER JOIN
                      dbo.CATEGORY_LINKS ON dbo.DDB.categoryIdentifier = dbo.CATEGORY_LINKS.LinkedCategory
WHERE     (dbo.DDB.maxprints > dbo.DDB.prints) AND (SQRT(POWER(dbo.DDB.latitude - 29.935313, 2) 
                      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 BETWEEN 0 AND 20)
ORDER BY Expr1
GO

SELECT * FROM MyViewName

Open in new window

0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40324778
If the performance still not good enough then you can try to create the view with SCHEMABINDING.
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE VIEW MyViewName
AS
SELECT DISTINCT 
                      TOP (40) dbo.DDB.brandname, SQRT(POWER(dbo.DDB.latitude - 29.935313, 2) 
                      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 AS Expr1, dbo.DDB.locationName, 
                      dbo.DDB.streetLine1, dbo.DDB.city, dbo.DDB.state, dbo.DDB.postalCode, 
                      dbo.DDB.brandLogoName, dbo.DDB.locationIdentifier, dbo.DDB.offerIdentifier, 
                      dbo.DDB.description, dbo.DDB.categoryIdentifier, dbo.DDB.source
FROM         dbo.DDB INNER JOIN
                      dbo.CATEGORY_LINKS ON dbo.DDB.categoryIdentifier = dbo.CATEGORY_LINKS.LinkedCategory
WHERE     (dbo.DDB.maxprints > dbo.DDB.prints) AND (SQRT(POWER(dbo.DDB.latitude - 29.935313, 2) 
                      + POWER(dbo.DDB.longitude - (-90.068825), 2)) / 0.014457 BETWEEN 0 AND 20)
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX MyView_IDX  ON MyViewName (Expr1)
GO

SELECT * FROM MyViewName

Open in new window


Since your view have now a cluster index on Expr1 you shouldn't need the ORDER BY clause.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40325848
1) Do you have an identity or equivalent unique row column(s) on DDB that is indexed?

2) Can you add an index to DDB if you need to?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40325876
For example, assume there is an identity column named "id".

Then, first create a covering index for the subquery:

CREATE NONCLUSTERED INDEX DDB__IX_maxprints ON dbo.DDB ( maxprints, prints ) INCLUDE ( id, latitude, longitude )

Then run the main query:

SELECT DISTINCT TOP (40)
                      ddb.brandname, ddb_top_nn.Expr1, ddb.locationName,
                      ddb.streetLine1, ddb.city, ddb.state, ddb.postalCode,
                      ddb.brandLogoName, ddb.locationIdentifier, ddb.offerIdentifier,
                      ddb.description, ddb.categoryIdentifier, ddb.source
FROM (
    SELECT TOP (200)
                          SQRT(POWER(ddb2.latitude - 29.935313, 2) + POWER(ddb2.longitude - (-90.068825), 2)) / 0.014457 AS Expr1,
                          ddb2.id
    FROM         dbo.DDB AS ddb2
    WHERE     (ddb2.maxprints > ddb.prints) AND
              (SQRT(POWER(ddb2.latitude - 29.935313, 2) + POWER(ddb2.longitude - (-90.068825), 2)) / 0.014457 BETWEEN 0.0 AND 20.0)
    ORDER BY         Expr1
) AS ddb_top_nn
INNER JOIN dbo.DDB AS ddb ON ddb.id = ddb_top_nn.id
ORDER BY         ddb_top_nn.Expr1
0
 

Author Closing Comment

by:SimpleDude
ID: 40327677
Paul, that worked perfect. Thanks a lot!

Also, thank you all for the proposed solutions.

Paula
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question