Fwd: Very slow query on Persisted Column with Function call

[Corneliu I. Tusnea]

Hi,

I have a table with a persisted column that is build using a function call:

This is the function:
CREATE FUNCTION os.RemoveNonAlphaNumericCharacters(
@Temp NVARCHAR(2048)
)
RETURNS VARCHAR(2048)
WITH SCHEMABINDING
AS
BEGIN

DECLARE @KeepValues AS NVARCHAR(100)
SET @KeepValues = '%[^a-z^0-9]%'
WHILE PatIndex(@KeepValues, @Temp) > 0
SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

RETURN @Temp
END

and this is the column:
IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = N'NormalizedName' AND
OBJECT_ID = OBJECT_ID(N'dbo.Product'))
BEGIN
ALTER TABLE dbo.Product
-- Maximum Index is 900
ADD NormalizedName AS LEFT(os.RemoveNonAlphaNumericCharacters(Name), 500
)
PERSISTED
END

Then I have an index on that column:
CREATE NONCLUSTERED INDEX IX_Product_AccountId_NormalizedName ON
dbo.Product
(
AccountId,
NormalizedName
) INCLUDE(Name)
WITH (ONLINE = ON)

All quite simple really.

When I try to execute a simple search using that column the search is very
very slow and I can see in the list of executing session a call to the os.
RemoveNonAlphaNumericCharacters

SELECT TOP 1 * FROM dbo.Product P WITH(NOLOCK)
WHERE P.BusinessFileId = 51678
AND P.NormalizedName = 'test'   -- should use the index


This is what I see is running:


I'm stuck and I don't know how to fix this atm.
I really expected the function to be called only during the insert (or
maybe updates) and not during the search.

I could remove the calculated column, clean the value in C# before I save
it and remove the function all together but that requires a fair bit of
code changes.

Thoughts?

PS> This is running on an Azure SQL P4 DB.

Thanks,
Corneliu

Re: Log filter / display dashboard - Looking for recommendation [Slightly off topic]

[Corneliu I. Tusnea]

Greg,

I've been looking into this as well and I'm not sure if there is a
one-size-fits all.

We've been using Seq in our app and we ingest ~1-6gb logs a day. Found seq
ok but if you search ... well, anything past 24-48h is very very slow. We
did various optimizations and move lots of the logs out of seq but still
not going where we wanted it.
I'm sure with a bigger machine we could have made it faster but I can't pay
$500 for a VM ingesting logs.
What I think seq is missing is a way to declare some sort of indexes
because now it indexes & searches everything. I only have 3-4 columns that
are my Primary Key and 99% of the searches have one of those columns in
them.

We've moved some of our logs into Azure Tables. Cheap and chips. Because we
have the PK as above we made those PK in Azure Tables. Search is fast,
maybe a bit limited as we have to search for stuff like contains in the
strings but cheap and scalable.

We area also using Azure Application Insighs for everything not log related
(events, metrics, ...) that we report from inside the app.

I'm now looking to deploy this on top of Application Insights:
https://www.microsoft.com/developerblog/2017/09/26/custom-analytics-dashboard-application-insights/

Looks very customizable and if you track the right data you migth pull out
the right reports.
I don't think you want to generate reports from logs. I would add telemetry
information inside the app using App Insights and use that for reports and
use the logs purely for diagnosing purposes.

Regards,
Corneliu.













On Tue, Mar 13, 2018 at 10:52 AM, Greg Harris 
wrote:

> Hi All,
>
> Question: Can you recommend a standard dashboard app that takes multiple
> logs, filters out the 99% that is not relevant, works out a status,
> displays a panel of Green, Amber, Red blobs with hyperlinks to detailed log
> info?
>
> Details:
>
> I am doing an architecture consulting gig for a client (a mostly MS
> Windows / Azure site, but there are some Unix servers out there as well).
>
> They have about a dozen core applications that the business is highly
> dependent on and another 100 or so that they are less dependent on.
>
> The problem being that when something goes wrong, vendor A says “the
> problem is with vendor B’s system” and you know what vendor B says.  What
> makes this worse is that the chain goes through multiple vendors and
> systems with long inter system data flows.
>
> I want to make the recommendation to them that they implement logging and
> a top level log display dashboard for the systems they are highly dependent
> on.  The dashboard would show:
>
> 1.   Dataflow around the systems
>
> 2.   Work backlog at each sub system
>
> 3.   Status at each sub system
>
> 4.   Time since last ping at each sub system
>
> This feels to me to be a standard sort of system that every major site
> needs!
>
> I am not sure what to recommend???
>
> Question: Can you recommend a standard dashboard app that takes multiple
> logs, filters out the 99% that is not relevant, works out a status,
> displays a panel of Green, Amber, Red blobs with hyperlinks to detailed log
> info?
>
> I am thinking of Nicholas Blumhardt’s Seq application (https://getseq.net/)
> or Datadog (https://www.datadoghq.com/) but neither of these feel quite
> complete to me.
>
> We will need a hierarchy solution:
>
> 1.   App logging (has to be baked into existing and new apps)
>
> 2.   Forwarded to Site Logger (maybe baked into the app, or as an add
> on)
>
> 3.   Site Logger
>
> 4.   Filter
>
> 5.   Analysis
>
> 6.   Dashboard display
>
> At level 1 if logging needs to be added to an existing or new system, I am
> thinking that the recommendation will be to use Serilog (
> https://github.com/serilog) or Datadog (https://www.datadoghq.com/).
>
> If the app already has logging, I am thinking that some form of log
> forwarder is needed, I want to avoid paying for apps to be modified to meet
> this need if they already have logging.
>
> Levels 2-6 could be should be a standard off the shelf app.
>
> Any advice would be greatly appreciated.
>
> Best Regards
>
> Greg Harris
> Greg Harris
> harris.gre...@gmail.com
>
>
>
>

RE: Surface Hub

[Chris Walsh]

It is really only an SMB/Enterprise Only device, heavily tied to SfB/Teams for 
proper collab.

From: ozdotnet-boun...@ozdotnet.com  On Behalf 
Of David Connors
Sent: Tuesday, 13 March 2018 9:39 PM
To: ozDotNet 
Subject: Re: Surface Hub

I'd like 1 for us and 1 for a customer. The fact these things are still in the 
enterprise sales model is a bit of a fail.



On Tue, 13 Mar 2018 at 20:35 Chris Walsh 
> wrote:
Bought them (25x55” & 10x84”) via Telstra, depends on how many you want to buy 
really.  If you buy 10 you get a good price, they (Microsoft) do end of quarter 
deals etc.


From: ozdotnet-boun...@ozdotnet.com 
> On Behalf 
Of David Connors
Sent: Monday, 12 March 2018 8:06 PM
To: ozDotNet >
Subject: Re: Surface Hub

Where did you buy it and what did you pay?
On Mon., 12 Mar. 2018, 6:58 pm Chris Walsh, 
> wrote:
Have a few yes.  What would you like to know?

From: ozdotnet-boun...@ozdotnet.com 
> On Behalf 
Of David Connors
Sent: Wednesday, 7 March 2018 3:01 PM
To: ozDotNet >
Subject: Surface Hub

Has anyone on this list actually bought a 55 inch surface hub?

--
David Connors
da...@connors.com | @davidconnors | 
https://t.me/davidconnors | LinkedIn | +61 417 189 
363
--
David Connors
da...@connors.com | @davidconnors | 
https://t.me/davidconnors | LinkedIn | +61 417 189 
363
--
David Connors
da...@connors.com | @davidconnors | 
https://t.me/davidconnors | LinkedIn | +61 417 189 363

Re: Surface Hub

[David Connors]

I'd like 1 for us and 1 for a customer. The fact these things are still in
the enterprise sales model is a bit of a fail.



On Tue, 13 Mar 2018 at 20:35 Chris Walsh  wrote:

> Bought them (25x55” & 10x84”) via Telstra, depends on how many you want to
> buy really.  If you buy 10 you get a good price, they (Microsoft) do end of
> quarter deals etc.
>
>
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *David Connors
> *Sent:* Monday, 12 March 2018 8:06 PM
> *To:* ozDotNet 
> *Subject:* Re: Surface Hub
>
>
>
> Where did you buy it and what did you pay?
>
> On Mon., 12 Mar. 2018, 6:58 pm Chris Walsh,  wrote:
>
> Have a few yes.  What would you like to know?
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *David Connors
> *Sent:* Wednesday, 7 March 2018 3:01 PM
> *To:* ozDotNet 
> *Subject:* Surface Hub
>
>
>
> Has anyone on this list actually bought a 55 inch surface hub?
>
>
>
> --
>
> David Connors
> da...@connors.com | @davidconnors | https://t.me/davidconnors
> | LinkedIn | +61 417 189 363 <+61%20417%20189%20363>
>
> --
>
> David Connors
> da...@connors.com | @davidconnors | https://t.me/davidconnors
> | LinkedIn | +61 417 189 363 <+61%20417%20189%20363>
>
-- 
David Connors
da...@connors.com | @davidconnors | https://t.me/davidconnors | LinkedIn | +61
417 189 363

RE: Surface Hub

[Chris Walsh]

Bought them (25x55” & 10x84”) via Telstra, depends on how many you want to buy 
really.  If you buy 10 you get a good price, they (Microsoft) do end of quarter 
deals etc.


From: ozdotnet-boun...@ozdotnet.com  On Behalf 
Of David Connors
Sent: Monday, 12 March 2018 8:06 PM
To: ozDotNet 
Subject: Re: Surface Hub

Where did you buy it and what did you pay?
On Mon., 12 Mar. 2018, 6:58 pm Chris Walsh, 
> wrote:
Have a few yes.  What would you like to know?

From: ozdotnet-boun...@ozdotnet.com 
> On Behalf 
Of David Connors
Sent: Wednesday, 7 March 2018 3:01 PM
To: ozDotNet >
Subject: Surface Hub

Has anyone on this list actually bought a 55 inch surface hub?

--
David Connors
da...@connors.com | @davidconnors | 
https://t.me/davidconnors | LinkedIn | +61 417 189 363
--
David Connors
da...@connors.com | @davidconnors | 
https://t.me/davidconnors | LinkedIn | +61 417 189 363