Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

performance issue causing application failed to load Expand / Collapse
Author
Message
Posted Monday, August 18, 2014 8:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 808, Visits: 1,443
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.
Post #1604482
Posted Monday, August 18, 2014 8:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1604486
Posted Monday, August 18, 2014 9:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 808, Visits: 1,443
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.
Post #1604502
Posted Monday, August 18, 2014 9:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
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.


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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1604508
Posted Monday, August 18, 2014 9:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 808, Visits: 1,443
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.


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
Post #1604511
Posted Monday, August 18, 2014 9:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
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.


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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1604518
Posted Monday, August 18, 2014 9:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 808, Visits: 1,443


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"
Post #1604536
Posted Monday, August 18, 2014 10:05 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
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"


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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1604541
Posted Monday, August 18, 2014 10:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 808, Visits: 1,443
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"


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.
Post #1604553
Posted Monday, August 18, 2014 10:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
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"


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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1604566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse