New Topic

Community Topic Idea Data Sources

David Registro

APAC SD Team

Assigned

Category Data Sources

Product Version 7.4

Remove the 'N' from WHERE clause when not NVARCHAR

Tal Mickel shared this idea ago

Completed

YF treats string values as NVARCHAR when using in where clause. This can cause performance slow down.

Can we please only have Yellowfin include the 'N' when querying NVARCHAR columns?

Here is example of query being run against VARCHAR column.

Select DISTINCT 
"First Name", "Last Name"
FROM "Users"
WHERE "Region" IN (N'APAC', N'NA')

I like this idea 0

Replies (5)

David Registro ●

Thanks Tal/Alex, I've spoken to a few devs over here and we will go ahead and raise an enhancement request for this.

If there are any updates on this I'll let you know, though please let us know if there was anything else in the meantime.

Regards,

David

Reply URL

Dor David Makmal ●

Hey David !

Is there any update for this idea ?

Reply URL

David Registro ●

Hi Dor,

Not as yet sorry, it is still in the list so it hasn't been forgotten about. Just not something that has been planned as yet.

Sorry for the vague info.

How many reports do you think are impacted by this, and in general how many slow reports do you have?

Thanks,

David

URL

Dor David Makmal ●

Hey Dave, sorry for my delay

It's in all of our reports, about 2500 reports...

It affects the performance of all the reports, so there's no specific slow reports..

URL

David Registro ●

Thanks Dor for confirming. I do know we have had a recent focus on performance improvements so I would expect some news on this soonish.

Regards,

David

URL

Dor David Makmal ●

awesome David. Thank you!

URL

David Registro ●

Hi Dor,

Just providing an update on this, we have put in a change which will make it into 7.4.11 though would like to get an interim out to you for testing.

If this is ok just shoot me an email and I'll provide you with a temp download link.

Thanks,

David

URL

Mike Sheehan ●

Hi Dor,

I know it's been a while since this was last brought up, but I believe it was mentioned this makes reports take a few more seconds to load when this is the scenario. Do you have an example of this occurring that you can show us? Do you perhaps have an example to provide us, via screenshots I suppose, where a query with NVARCHAR that's not an actual NVARCHAR column takes just a few seconds to execute in SQL Server, but takes significantly longer in Yellowfin? Are we talking 5 seconds vs. 45 seconds? Or like 5 seconds vs. 10 seconds?

Thanks for your feedback.

Regards,

Mike

Reply URL

Dor David Makmal ●

Hey Mike,

I've contact our department's DEV team to give us an example for that. I'll update once we have an example.

URL

Dor David Makmal ●

Hey Mike,

I see that our DEV team once contacted you and presented the problem.

You can see it in ticket number 6013 (not shared due to public topic)

Let me know if it's alright

Reply URL

David Registro ●

Hi Dor,

Yup I can see that info, though it doesn't seem to outline any big problems.

In the test case you're using 200,000,000 rows of data (Which I'm sure will not be a normal amount of data).

The differences seem to be minimal.

With N'Varchar conversion: 4738 ms.

Without the conversion: 783 ms.

So yes it's x4 slower, but we're talking 4secs for 20 million rows of data.

I don't think this would have any real impact for your users unless they're constantly running reports that return more than 20 million rows, even then we're talking a matter of seconds difference.

Am I missing something here?

Thanks,

David

URL

Richard Convery ●

Following conversation with Dor, this is complete and is being closed. There is a following conversation about this and Dor is going to raise a new ticket.

Reply URL