July 26, 2014 at 9:48 am
attachment uploaded
July 26, 2014 at 10:15 am
I recommend the following steps to change it into a remote query:
1) change the main query completely into a dynamic query so it will run when using EXECUTE(@sql) where @sql is the string holding the main query. This will require to replace all single quotation marks with two single quotation marks and to add a quotation mark at the beginnig and at the end.
2) Verify, the query returns the same data as previously.
3) Remove each and every occurence of ENIQSQLSERVER. (including the dot, this will change the table reference to a three-part-named reference).
4) Execute the query remote using EXECUTE(@sql) AT ENIQSQLSERVER
5) see what happens 😉
July 26, 2014 at 11:39 am
Hi,
As I mentioned in my earlier post while running with OpenQuery gives me the following error:
The OLE DB provider "SAOLEDB.11" for linked server "ENIQSQLSERVER" supplied inconsistent metadata. The object "(user generated expression)" was missing the expected column "Bmk1000".
Any possible cause of it.
Thanks,
July 26, 2014 at 11:44 am
mallikachowdhary 98955 (7/26/2014)
Hi,As I mentioned in my earlier post while running with OpenQuery gives me the following error:
The OLE DB provider "SAOLEDB.11" for linked server "ENIQSQLSERVER" supplied inconsistent metadata. The object "(user generated expression)" was missing the expected column "Bmk1000".
Any possible cause of it.
Thanks,
The steps I recommended did not include the word "OpenQuery". just "EXECUTE() AT..."
Furthermore, in your earlier post you've just used OpenQuery for a part of the whole query and I recommended to run the complete query at the source server.
Please post the code you've used (if it is any different than the one you already provided).
July 26, 2014 at 12:06 pm
Thanks Lutz for the recommendation...Can you please send the steps or the final query to run.
As it is the production server I'm not allowed to make frequent changes. Also starting with my initial query what checks can be performed that could be the possible cause of a query running so slow at one server with same configuration. Any possible things that can be checked at the hardware or the software application side.
Thanks in advance.
July 29, 2014 at 2:17 am
The easiest solution would be to save the query as a view on server ENIQSQLSERVER.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2014 at 2:27 am
mallikachowdhary 98955 (7/26/2014)
Thanks Lutz for the recommendation...Can you please send the steps or the final query to run.As it is the production server I'm not allowed to make frequent changes. Also starting with my initial query what checks can be performed that could be the possible cause of a query running so slow at one server with same configuration. Any possible things that can be checked at the hardware or the software application side.
Thanks in advance.
The execution plans aren't generated by the server hosting the data you are reading, they are generated by the server(s) talking to it. The two execution plans are quite different, the slow version performs a whole table scan for one of the tables. That means the whole table is read from ENIQSQLSERVER to the server running the query. Part of the other table is fetched too, then the joins and aggregates are performed locally on the calling server - with no indexes to support the processing.
The whole point of either OPENQUERY or EXECUTE() AT is to perform the whole of the query on ENIQSQLSERVER and return the aggregated result to the calling server. Whichever method you choose to use, the performance gains are likely to be dramatic.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2014 at 2:29 am
Hi Chris,
The query I attached is defined and saved as a procedure and there is a job defined which executes this procedure daily. Are you referring that this procedure should be pointing to a view.
Apart from this can you help me if any other aspect can be explored (internal memory etc.) while comparing the 2 systems.
Thanks in advance
July 29, 2014 at 2:37 am
It's a procedure on the calling server. You have a few options here:
Save the query as a view on the remote server and call the view from the procedure.
Change the entire query in the procedure into a string and use either OPENQUERY() or EXECUTE() AT to execute the query as a "SQL pass-thru" query on the remote server.
Whichever method you use, only running the entire query on the remote server will make a noticeable difference, but it could bring the time down from a few hours to a few minutes.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2014 at 3:30 am
Sure Chris, I'll try this right away.
I would like to share a new learning I made today , earlier when I was trying to run the query with OPENQUERY command I used to get this error:
The OLE DB provider "SAOLEDB.11" for linked server "ENIQSQLSERVER" supplied inconsistent metadata. The object "(user generated expression)" was missing the expected column "Bmk1000".
While comparing the properties of two linked server I found that the one showing error has this option of "Collation Compatible " marked as False . I have turned it to True and have run the query , still its executing and have not given the above error. 🙂
July 31, 2014 at 2:01 am
mallikachowdhary 98955 (7/29/2014)
Sure Chris, I'll try this right away.I would like to share a new learning I made today , earlier when I was trying to run the query with OPENQUERY command I used to get this error:
The OLE DB provider "SAOLEDB.11" for linked server "ENIQSQLSERVER" supplied inconsistent metadata. The object "(user generated expression)" was missing the expected column "Bmk1000".
While comparing the properties of two linked server I found that the one showing error has this option of "Collation Compatible " marked as False . I have turned it to True and have run the query , still its executing and have not given the above error. 🙂
So how did you get on?
Here's an alternative way to write your query which cuts the complexity in half. Note that I've only included about 10% of the output rows for clarity:
INSERT INTO [QMIS_KPI120_DB].[dbo].[00_INPUT_ER_RSSI_TX_CARRIER_POWER_INSERTION]
SELECT
LocalCellID
RNC,
Start_Time,
DC_RELEASE,
Start_Date,
SUM(Case when DCVECTOR_INDEX = 0 then pmAverageRssi ELSE 0 End) as pmAverageRssi_0,
SUM(Case when DCVECTOR_INDEX = 0 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_0,
SUM(Case when DCVECTOR_INDEX = 10 then pmAverageRssi ELSE 0 End) as pmAverageRssi_10,
SUM(Case when DCVECTOR_INDEX = 10 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_10,
SUM(Case when DCVECTOR_INDEX = 20 then pmAverageRssi ELSE 0 End) as pmAverageRssi_20,
SUM(Case when DCVECTOR_INDEX = 20 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_20,
SUM(Case when DCVECTOR_INDEX = 30 then pmAverageRssi ELSE 0 End) as pmAverageRssi_30,
SUM(Case when DCVECTOR_INDEX = 30 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_30,
SUM(Case when DCVECTOR_INDEX = 40 then pmAverageRssi ELSE 0 End) as pmAverageRssi_40,
SUM(Case when DCVECTOR_INDEX = 40 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_40,
SUM(Case when DCVECTOR_INDEX = 50 then pmAverageRssi ELSE 0 End) as pmAverageRssi_50,
SUM(Case when DCVECTOR_INDEX = 50 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_50,
SUM(Case when DCVECTOR_INDEX = 60 then pmAverageRssi ELSE 0 End) as pmAverageRssi_60,
SUM(Case when DCVECTOR_INDEX = 60 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_60,
SUM(Case when DCVECTOR_INDEX = 64 then pmAverageRssi ELSE 0 End) as pmAverageRssi_64,
SUM(Case when DCVECTOR_INDEX = 64 then pmTransmittedCarrierPower ELSE 0 End) as pmTransmittedCarrierPower_64
FROM (
SELECT -- DISTINCT -- avoid using DISTINCT with an aggregating query
E.UCELL_ID As LocalCellID
,RNC As RNC
,Cast( Cast(YEAR_ID As varchar(4)) + '-' + Cast(MONTH_ID As varchar(2)) + '-' + Cast(DAY_ID As varchar(2)) + ' ' + Cast(HOUR_ID As varchar(2)) + ':00:00' As datetime) As Start_Time
,Cast( Cast(YEAR_ID As varchar(4)) + '-' + Cast(MONTH_ID As varchar(2)) + '-' + Cast(DAY_ID As varchar(2)) + ' ' + '00:00:00' As datetime) As Start_Date
,RBS As RbsUtranId
,H.Sector As Sector
,DCVECTOR_INDEX As DCVECTOR_INDEX
,MIN(H.DC_RELEASE) as DC_RELEASE
,Sum(pmAverageRssi) As pmAverageRssi
,Sum(pmTransmittedCarrierPower) As pmTransmittedCarrierPower
FROM ENIQSQLSERVER.DWHDB.dc.DC_E_RBS_CARRIER_V_RAW H
INNER Join ( -- e
SELECT DISTINCT
RBS_ID,
Ucell_ID,
RNC_ID,
LOCALCellID
FROM ENIQSQLSERVER.DWHDB.DC.DIM_E_RAN_UCELL
WHERE STATUS = 'ACTIVE'
--SELECT Distinct RBS_ID,Ucell_ID,RNC_ID,LOCALCellID FROM OPENQUERY(ENIQSQLSERVER, 'SELECT * FROM ENIQSQLSERVER.DWHDB.DC.DIM_E_RAN_UCELL Where STATUS = ''ACTIVE''')
) e
ON H.RBS = E.RBS_ID
And H.RNC = E.RNC_ID
And E.LOCALCellID = H.Sector
WHERE ROWSTATUS = 'LOADED'
--and ROWSTATUS <> 'DUPLICATE' -- unnecessary
--and ROWSTATUS <> 'SUSPECTED' -- unnecessary
AND DATE_ID = CAST(DATEADD(D, -1, GETDATE()) AS DATE)
GROUP BY
e.UCELL_ID
,RBS
,H.Sector
,DCVECTOR_INDEX
,YEAR_ID
,MONTH_ID
,DAY_ID
,HOUR_ID
,H.DC_RELEASE
,RNC
) Hourly
WHERE 1 = 1
--AND LocalCellID is not null -- unnecessary
--and RNC is not null -- unnecessary
and Start_Time is not null
and Start_Date is not null
and RbsUtranId is not null
--and Sector is not null -- unnecessary
and DC_RELEASE is not null
and LOCALCellID is not Null
and LOCALCellID not like ' %'
and LOCALCellID not like '%BEA%'
and LOCALCellID not like '%BSC%'
and LOCALCellID not like '%BTS%'
and LOCALCellID not like '%CB%'
and LOCALCellID not like '%CWH%'
and LOCALCellID not like '%G655%'
and LOCALCellID not like '%NULL%'
and LOCALCellID not like '%RM%'
and LOCALCellID not like '%RNC%'
and LOCALCellID not like '%TB%'
and LOCALCellID not like '%TEST%'
and LOCALCellID not like '%TST%'
and LOCALCellID not like '%WH%'
and LOCALCellID not like '655%'
and LOCALCellID not like 'B%'
GROUP BY
LocalCellID
,RNC
,Start_Time
,Start_Date
,DC_RELEASE
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2014 at 4:34 am
Hi Chris,
I had run the original query yesterday with OpenQuery Status= Active filter to check the result , its still running after 23 hrs 🙁
I'm waiting for it to complete than I'll modify the query as suggested by you and let u know the results.
In the mean time do u have any idea why the query is behaving differently when u saw the execution plan. Is there an internal memory overflow or any application setting making the system slow.
Thanks again for the help and happy weekend.
August 1, 2014 at 5:12 am
That's a bit rubbish isn't it. Try this if you get the opportunity. It's only a select, no update:
DECLARE @tsql VARCHAR(8000)
SET @tsql = '
SELECT
E.UCELL_ID As LocalCellID
,RNC As RNC
,Cast( Cast(YEAR_ID As varchar(4)) + ''-'' + Cast(MONTH_ID As varchar(2)) + ''-'' + Cast(DAY_ID As varchar(2)) + '' '' + Cast(HOUR_ID As varchar(2)) + '':00:00'' As datetime) As Start_Time
,Cast( Cast(YEAR_ID As varchar(4)) + ''-'' + Cast(MONTH_ID As varchar(2)) + ''-'' + Cast(DAY_ID As varchar(2)) + '' '' + ''00:00:00'' As datetime) As Start_Date
,RBS As RbsUtranId
,H.Sector As Sector
,DCVECTOR_INDEX As DCVECTOR_INDEX
,MIN(H.DC_RELEASE) as DC_RELEASE
,SUM(pmAverageRssi) As pmAverageRssi
,SUM(pmTransmittedCarrierPower) As pmTransmittedCarrierPower
FROM DWHDB.dc.DC_E_RBS_CARRIER_V_RAW H
INNER Join ( -- e
SELECT DISTINCT
RBS_ID,
Ucell_ID,
RNC_ID,
LOCALCellID
FROM DWHDB.DC.DIM_E_RAN_UCELL
WHERE STATUS = ''ACTIVE''
) e
ON H.RBS = E.RBS_ID
And H.RNC = E.RNC_ID
And E.LOCALCellID = H.Sector
WHERE ROWSTATUS = ''LOADED''
AND DATE_ID = CAST(DATEADD(D, -1, GETDATE()) AS DATE)
GROUP BY e.UCELL_ID,RBS,H.Sector,DCVECTOR_INDEX,YEAR_ID,MONTH_ID,DAY_ID,HOUR_ID,H.DC_RELEASE,RNC'
PRINT @tsql
SELECT TOP 100 * FROM OPENROWSET (ENIQSQLSERVER, @tsql) Hourly
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2014 at 6:07 am
The query above gave the following error:
Incorrect syntax near the keyword 'OPENROWSET'.
August 1, 2014 at 6:17 am
My apologies, that should be OPENQUERY not OPENROWSET.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply