SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


performance issue causing application failed to load


performance issue causing application failed to load

Author
Message
halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1977 Visits: 1744
Good day,

In a project I am working on a database with only one table: the table has 100 millions records

The performance issue is causing the front-end failed to load, restricted by the application, I have to create a virtual table (like staging table) to get the data from the original table. The way to create that staging table is to use select query and only so-called standard select query is allowed (no CTE, no declare, no Order By...), here is the query I am using for now: the query works but like I said, performance is no good and is most likely causing the app failed to load:

select
date_time as DateAndTime
,CASE
WHEN CHARINDEX(CHAR(46),REVERSE(url),CHARINDEX(CHAR(46),REVERSE(url),1) + 1) = 0 and ISNUMERIC(REPLACE(url,CHAR(46),'')) = 0 THEN url
WHEN CHARINDEX(CHAR(46),REVERSE(url),CHARINDEX(CHAR(46),REVERSE(url),1) + 1) > 0 and ISNUMERIC(REPLACE(url,CHAR(46),'')) = 0 THEN RIGHT(url,CHARINDEX(CHAR(46),REVERSE(url),CHARINDEX(CHAR(46),REVERSE(url),1) + 1) - 1)
END As NewURL
,[user]
,SUM(bytes_sent) as TotalSent
,SUM(bytes_received) as TotalReceived
,SUM(CASE WHEN disposition <> 'Category1' AND disposition <> 'Category2' THEN 1 ELSE 0 END) AS TotalPermitted
,SUM(CASE WHEN disposition = 'Category1' OR disposition = 'Category2' THEN 1 ELSE 0 END) AS TotalBlocked
From
dbo.SHN_WSS_LogDetails
Where category = 'Bandwidth - PNSB' AND [user] is not null and url is not null
Group By
date_time
, [user]
, CASE
WHEN CHARINDEX(CHAR(46),REVERSE(url),CHARINDEX(CHAR(46),REVERSE(url),1) + 1) = 0 and ISNUMERIC(REPLACE(url,CHAR(46),'')) = 0 THEN url
WHEN CHARINDEX(CHAR(46),REVERSE(url),CHARINDEX(CHAR(46),REVERSE(url),1) + 1) > 0 and ISNUMERIC(REPLACE(url,CHAR(46),'')) = 0 THEN RIGHT(url,CHARINDEX(CHAR(46),REVERSE(url),CHARINDEX(CHAR(46),REVERSE(url),1) + 1) - 1)
END



Please note the CASE clause is needed to sort out the urls in different formats like

('www.bbc.co.uk')
,('bbc.co.uk')
,('part.of.bbc.co.uk')
,('www.microsoft.com')
,('part.of.microsoft.com')
,('intel.com')
,('rai.it')
,('192.168.2.321')
,('m.mbl.is');



to two parts only domain:
co.uk
co.uk
co.uk
microsoft.com
microsoft.com
intel.com
rai.it
mbl.is



But, will it be more efficient to use a user-defined function for that CASE clause?

I do not have privilege to test it out on the original database/server, I want to hear your input on this.

Thanks to everyone for the discussion, any clue is appreciated.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32653 Visits: 18558
No. A udf in that case clause would not be better. You are grouping by something that needs to be "calculated."

Instead, why not dump that information into a cte, or into a temp table. Then select from the temp table or CTE. In that final select, you can do your grouping on the column name rather than the case statement.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1977 Visits: 1744
unfortunately CTE is not allowed in this particular staging table creation, and even if it is allowed, 10 million records would be a lengthy dump.Crazy
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32653 Visits: 18558
halifaxdal (8/18/2014)
unfortunately CTE is not allowed in this particular staging table creation, and even if it is allowed, 10 million records would be a lengthy dump.Crazy


So use a temp table.

Using that case statement (or a udf) in the group by is a recipe for causing your query to perform terribly.

Dumping to a temp table will often solve that.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1977 Visits: 1744
SQLRNNR (8/18/2014)
halifaxdal (8/18/2014)
unfortunately CTE is not allowed in this particular staging table creation, and even if it is allowed, 10 million records would be a lengthy dump.Crazy


So use a temp table.

Using that case statement (or a udf) in the group by is a recipe for causing your query to perform terribly.

Dumping to a temp table will often solve that.


Sorry I should have mentioned that temp is also not allowed: only "select", no declare, no CTE, no temp, no variables, no ORDER
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32653 Visits: 18558
halifaxdal (8/18/2014)
SQLRNNR (8/18/2014)
halifaxdal (8/18/2014)
unfortunately CTE is not allowed in this particular staging table creation, and even if it is allowed, 10 million records would be a lengthy dump.Crazy


So use a temp table.

Using that case statement (or a udf) in the group by is a recipe for causing your query to perform terribly.

Dumping to a temp table will often solve that.


Sorry I should have mentioned that temp is also not allowed: only "select", no declare, no CTE, no temp, no variables, no ORDER


And why would you want to handcuff yourself like that?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1977 Visits: 1744


And why would you want to handcuff yourself like that?


I like the word "handcuff", that's not by me, it's by the platform itself, I am building a dashboard using Dundas Dashboard:

When you do not have database privilege, you end up building virtual table through manual method: which means only "pure" select query is allowed with lots of restrictions, and yes, exactly like being "handcuffed"w00tCrying
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32653 Visits: 18558
halifaxdal (8/18/2014)


And why would you want to handcuff yourself like that?


I like the word "handcuff", that's not by me, it's by the platform itself, I am building a dashboard using Dundas Dashboard:

When you do not have database privilege, you end up building virtual table through manual method: which means only "pure" select query is allowed with lots of restrictions, and yes, exactly like being "handcuffed"w00tCrying


A dashboard that needs to consume 10 million records? That seems like a (to be frank) useless venture. Nobody reviews 10 million records on a report or in a dashboard. If it is a dashboard, it should be pre-aggregated and quickly consumable into a high level style report.

I'd recommend working with the DBAs who do have access. Show them the query and the table definition that would work. Dump your data into a staging table. Select from that staging table (with the re-defined urls) and group on that.

Otherwise, try a subquery in a join. But to be fair on that one, you are still consuming 10 million records in a dashboard. People expect that to be fairly snappy. Since you are consuming 10 million records, there will be some wait involved.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1977 Visits: 1744
SQLRNNR (8/18/2014)
halifaxdal (8/18/2014)


And why would you want to handcuff yourself like that?


I like the word "handcuff", that's not by me, it's by the platform itself, I am building a dashboard using Dundas Dashboard:

When you do not have database privilege, you end up building virtual table through manual method: which means only "pure" select query is allowed with lots of restrictions, and yes, exactly like being "handcuffed"w00tCrying


A dashboard that needs to consume 10 million records? That seems like a (to be frank) useless venture. Nobody reviews 10 million records on a report or in a dashboard. If it is a dashboard, it should be pre-aggregated and quickly consumable into a high level style report.

I'd recommend working with the DBAs who do have access. Show them the query and the table definition that would work. Dump your data into a staging table. Select from that staging table (with the re-defined urls) and group on that.

Otherwise, try a subquery in a join. But to be fair on that one, you are still consuming 10 million records in a dashboard. People expect that to be fairly snappy. Since you are consuming 10 million records, there will be some wait involved.


In reality there are cases that you have to work with certain restrictions, and I am glad at least I can seek help from Internet when there is no DBA available beside you.

And yes, no kidding 10 million records, the dashboard is to report highly summarized info, I have other queries to calculate numbers not posted here.

I am more interested to see if who can do some magic work on my query to make it faster.

Thank you.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32653 Visits: 18558
halifaxdal (8/18/2014)
SQLRNNR (8/18/2014)
halifaxdal (8/18/2014)


And why would you want to handcuff yourself like that?


I like the word "handcuff", that's not by me, it's by the platform itself, I am building a dashboard using Dundas Dashboard:

When you do not have database privilege, you end up building virtual table through manual method: which means only "pure" select query is allowed with lots of restrictions, and yes, exactly like being "handcuffed"w00tCrying


A dashboard that needs to consume 10 million records? That seems like a (to be frank) useless venture. Nobody reviews 10 million records on a report or in a dashboard. If it is a dashboard, it should be pre-aggregated and quickly consumable into a high level style report.

I'd recommend working with the DBAs who do have access. Show them the query and the table definition that would work. Dump your data into a staging table. Select from that staging table (with the re-defined urls) and group on that.

Otherwise, try a subquery in a join. But to be fair on that one, you are still consuming 10 million records in a dashboard. People expect that to be fairly snappy. Since you are consuming 10 million records, there will be some wait involved.


In reality there are cases that you have to work with certain restrictions, and I am glad at least I can seek help from Internet when there is no DBA available beside you.

And yes, no kidding 10 million records, the dashboard is to report highly summarized info, I have other queries to calculate numbers not posted here.

I am more interested to see if who can do some magic work on my query to make it faster.

Thank you.


So no DBA available, and nobody with access to create a table into which you could dump your results to then aggregate it in this query?

Have you tried the subquery option that I proposed?

Nobody can really do much more to help since you haven't provided sample table structures and consumable data to try a different solution.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search