Showing posts with label feature. Show all posts
Showing posts with label feature. Show all posts

Friday, March 23, 2012

query plan caching?

SQL Server has the concept of caching the query plan. Parameterized queries can take advantage of this feature.

I'm wondering if there's an equivalent feature in SSAS. If I use MDX parameters (i.e. using @.Param in the query and setting the Parameters object of the AdomdCommand object) and run the same query twice with similar parameter values, will SSAS be able to reuse the query plan at all?

What I'm wondering is whether it is better (a) to use parameters and then StrToSet to convert those parameters into MDX objects... OR (b) to just run dynamic MDX with no parameters. (Yes, I'm referring to how Reporting Services does MDX queries. Wondering if it would be worth suggesting to the SSRS team that they take the parameter values and build dynamic MDX instead of using MDX parameters.)

There is no equivalent for cached query plans in the SSAS 2005 version.

> What I'm wondering is whether it is better (a) to use parameters and then StrToSet to convert those parameters into MDX objects... OR (b) to just run dynamic MDX with no parameters.

In SSAS 2005, building MDX on the fly is always better than using StrToSet and parameters.

|||

Thanks Mosha. That answered my question. I reported this as a suggestion for Katmai SSRS:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263472

|||I diagree that this is SSRS issue. SSRS is absolutely correct using StrToSet( @.param, CONSTRAINED ) for many reason, one being security. This should be reported as suggestion for Katmai SSAS - to optimize StrToXXX( , CONSTRAINED) family of functions.

Monday, March 12, 2012

Query Output Custom Delimiter

Hello,

I have a SSMS query result set that includes columns which contain commas. I used the save as feature in the Results pane in SSMS but saves it as a comma delimited file. I tried to change the delimiter using in Query Options/Custom Delmiter to semi colon however it still saves it with a comma delimiter.

Any suggestions on how to change the delimiter?

Thanks

Are you using the save to file option for the query or are you selecting all the data and then saving it?

I ran this test, and I got the commas into a single column. The difference is that I made the file type *.*, and used a .TXT extension.

CREATE DATABASE TEST;

GO

USE TEST;

GO

CREATE TABLE TestComma (column1 varchar(30));

GO

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test');

GO

-- Now run this to save output into text file:

SELECT * FROM TestComma;

GO

-- Use the Save as feature:

SELECT * FROM TestComma;

GO

|||I selected all data in the query results window then saving it. The only option is to save it as a .csv file and I was wondering if that can be changed.|||

As I mentioned, use the *.* file type, and then specify a name. Then it's just text and not formatted as a CSV.

Query Output Custom Delimiter

Hello,

I have a SSMS query result set that includes columns which contain commas. I used the save as feature in the Results pane in SSMS but saves it as a comma delimited file. I tried to change the delimiter using in Query Options/Custom Delmiter to semi colon however it still saves it with a comma delimiter.

Any suggestions on how to change the delimiter?

Thanks

Are you using the save to file option for the query or are you selecting all the data and then saving it?

I ran this test, and I got the commas into a single column. The difference is that I made the file type *.*, and used a .TXT extension.

CREATE DATABASE TEST;

GO

USE TEST;

GO

CREATE TABLE TestComma (column1 varchar(30));

GO

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test');

GO

-- Now run this to save output into text file:

SELECT * FROM TestComma;

GO

-- Use the Save as feature:

SELECT * FROM TestComma;

GO

|||I selected all data in the query results window then saving it. The only option is to save it as a .csv file and I was wondering if that can be changed.|||

As I mentioned, use the *.* file type, and then specify a name. Then it's just text and not formatted as a CSV.

Friday, March 9, 2012

Query optimization in query that uses CTE.

Hi Guys,

I want to optimize one query which uses the CTE (server 2005 feature).
I am sending you the abstract query.

currently this query take 4-5 seconds to execute.
but i want to reduce it to 1 sec.
Plz, do help me, if someone know how to do it.


--
DECLARE @.X INT
DECLARE @.LowerGradeRange INT
DECLARE @.UpperGradeRange INT
DECLARE @.Keyword NVARCHAR(500)

SET @.X = 11500001
SET @.LowerGradeRange = NULL
SET @.UpperGradeRange = NULL
SET @.Keyword = ''

IF ISNULL(@.Keyword,'')=''
SET @.Keyword='';


WITH SelPath (path_id,x,y,z,r)
AS
(

-- Anchor member definition (returns base result set)
SELECT path_id,x,y,z,r

FROM tab1 a
INNER JOIN tab2 b ON a.x= b.x

WHERE
a.x = @.X
-- AND (a.parent IS NULL OR a.parent = 0)
AND
CASE
WHEN ISNULL(@.LowerGradeRange,'')='' THEN 1
WHEN ISNULL(@.LowerGradeRange,'')<>'' AND b.lgr >= @.LowerGradeRange THEN 1
END=1
AND
CASE
WHEN ISNULL(@.UpperGradeRange,'')='' THEN 1
WHEN ISNULL(@.UpperGradeRange,'')<>'' AND b.ugr <= @.UpperGradeRange THEN 1

END=1
AND
CASE
WHEN @.Keyword <>'' AND b.y LIKE @.Keyword THEN 1
ELSE 1
END =1


UNION ALL

-- Recursive member definition
-- (returns the direct subordinate(s) of the activity in the anchor member result set)


SELECT path_id,x,y,z,r
FROM SelPath b
INNER JOIN tab1 a ON a.parent = b.path_id
INNER JOIN tab2 c ON a.x = c.x
WHERE
CASE
WHEN ISNULL(@.LowerGradeRange,'')='' THEN 1
WHEN ISNULL(@.LowerGradeRange,'')<>'' AND c.lgr >= @.LowerGradeRange THEN 1
END=1
AND
CASE
WHEN ISNULL(@.UpperGradeRange,'')='' THEN 1
WHEN ISNULL(@.UpperGradeRange,'')<>'' AND c.ugr <= @.UpperGradeRange THEN 1

END=1
AND
CASE
WHEN @.Keyword <>'' AND c.y LIKE @.Keyword THEN 1
ELSE 1
END =1

)

-- Statement that executes the CTE
SELECT path_id,x,y,z,r

FROM SelPath a
INNER JOIN pce.qq c ON a.r = c.r

ORDER BY x

--

Reply soon...
bye
take care

Regards,
-Surendra


Go to the T-SQL forum. You'll get more help there.

-Jamie