query performance – How can I achieve predicate pushdown in my view

I have a reporting table (about 1bn rows), and a tiny dimension table:

CREATE TABLE dbo.Sales_unpartitioned (
    BusinessUnit    int NOT NULL,
    (Date)          date NOT NULL,
    SKU             varchar(8) NOT NULL,
    Quantity        numeric(10, 2) NOT NULL,
    Amount          numeric(10, 2) NOT NULL,
    CONSTRAINT PK_Sales_unpartitioned PRIMARY KEY CLUSTERED (BusinessUnit, (Date), SKU)
);

--- Demo data:
INSERT INTO dbo.Sales_unpartitioned
SELECT severity AS BusinessUnit,
       DATEADD(day, message_id, '2000-01-01') AS (Date),
       LEFT((text), 3) AS SKU,
       1000.*RAND(CHECKSUM(NEWID())) AS Quantity,
       10000.*RAND(CHECKSUM(NEWID())) AS Amount
FROM sys.messages
WHERE (language_id)=1033;

--- Artificially inflate statistics of demo data:
UPDATE STATISTICS dbo.Sales_unpartitioned WITH ROWCOUNT=1000000000;

--- Dimension table:
CREATE TABLE dbo.BusinessUnits (
    BusinessUnit    int NOT NULL,
    SalesManager    nvarchar(250) NULL,
    PRIMARY KEY CLUSTERED (BusinessUnit)
);

INSERT INTO dbo.BusinessUnits (BusinessUnit)
SELECT DISTINCT BusinessUnit FROM dbo.Sales;

… to which I’ve added a reporting view used by an application for OLTP-style reporting.

CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned
AS

SELECT bu.BusinessUnit,
       s.(Date),
       s.SKU,
       s.Quantity,
       s.Amount
FROM dbo.BusinessUnits AS bu
CROSS APPLY (
    --- Regular sales
    SELECT t.BusinessUnit, t.(Date), t.SKU, t.Quantity, t.Amount
    FROM dbo.Sales_unpartitioned AS t
    WHERE t.BusinessUnit=bu.BusinessUnit
      AND t.SKU LIKE 'T%'

    UNION ALL

    --- This is a special reporting entry. We only
    --- want to see today's row. In case of duplicates,
    --- get the row with the first "SKU".
    SELECT TOP (1) s.BusinessUnit, s.(Date), s.SKU, s.Quantity, s.Amount
    FROM dbo.Sales_unpartitioned AS s
    WHERE s.BusinessUnit=bu.BusinessUnit
      AND s.(Date)=CAST(SYSDATETIME() AS date)
      AND s.SKU LIKE 'S%'
    ORDER BY s.BusinessUnit, s.(Date), s.SKU
) AS s

The idea is that the user application will query this view with a SELECT query that filters on a range of dates and one or more BusinessUnits. For this purpose, I’ve chosen a CROSS APPLY pattern, so that the query can “loop” over each BusinessUnit, seek to a range of Date, and apply a residual filter on SKU.

Example app query:

DECLARE @from date='2021-01-01', @to date='2021-12-31';

SELECT *
FROM dbo.SalesReport_unpartitioned
WHERE BusinessUnit=16
  AND (Date) BETWEEN @from AND @to
ORDER BY BusinessUnit, (Date), SKU;

I would expect a query plan that looks like this:
Desired query plan with predicate pushdown and no Filter operator
Desired plan

However, the plan turns out like this:
Actual query plan seeks only on BusinessUnit with residual predicate on SKU, adds Filter to the end of the plan
Actual plan

I expected SQL Server to do a “predicate pushdown” on the Date column, allowing the Clustered Index Seek to look for a single BusinessUnit and a range of Date, then apply a residual predicate on SKU. This works on the Seek in the “s” branch (the one with TOP) – probably because it has a hard-coded Date predicate in the query – but not on the “t” branch.

However, on the “t” branch SQL Server only seeks to the specific BusinessUnit with a residual predicate on SKU, effectively retrieving all dates. Only at the end of the plan does it applies a Filter operator that filters on the Date column.

In a large table, this has a very significant performance penalty – you could end up reading 20 years of data from disk when all you’re looking for is a week.

Things I’ve tried

Workarounds:

  • Converting the view to an inline table valued function with @fromDate and @toDate parameters that filter the “s” and “t” queries will enable a Seek on (BusinessUnit, Date) as desired, but requires rewriting the app code.
  • Moving the UNION ALL out of the CROSS APPLY (from CROSS APPLY (UNION) to CROSS APPLY() UNION CROSS APPLY()) will enable predicate pushdown. It makes one more seek on the BusinessUnit table, which is perfectly acceptable.

Fixes the Seek, but changes the results:

  • Surprisingly, removing the TOP (1) and ORDER BY for the “s” query makes predicate pushdown work on “t”, but can give return too many rows from “s”.
  • Eliminating UNION ALL by either removing the “s” or “t” query will enable predicate pushdown, but generate incorrect results.

No change or not feasible:

  • Replacing TOP (1) with a ROW_NUMBER() pattern does not change the Seek.
  • Changing the CROSS APPLY to a forced INNER LOOP JOIN fixes the Seek on “t”, but actually changes “s” to a Scan instead, which is even worse.
  • Adding trace flag 8780 to allow the optimizer to work on a plan for longer does not change anything. The plan is already optimized FULL with no early termination.

A common thread seems to be that changing/simplifying the “s” query (removing TOP, ORDER BY) fixes the problem on the “t” query, which feels counter-intuitive to me.

What I’m looking for

I’m trying to understand if this is a shortcoming of the optimizer, if it’s the result of a deliberate costing/optimization mechanism, or if I’ve simply overlooked something.

Best Way To Achieve Configurable Product? Is This A Configurable Product?

Maybe configurable product is not the appropriate term, but I’m looking to provide different product options that will dynamically update the URL/SKU/Price. I’ve included a perfect example below of what we’re trying to achieve. If it can be done OOTB, great – if it has to be an extension, that’s fine – I just can’t seem to find what I’m looking for.

enter image description here

Website of example shown

When you select the different options, the Item/SKU updates, the pricing updates and the URL changes. Ideally, we would have two simple products – but on each simple product you could select the other version, like the image above. Any insight would be greatly appreciated!

gn.general topology – Hypotesis on $W$ to achieve $d(x,F(W))

Writing a paper, I’m trying to formulate the following technical result:

Let $X$ be a manifold, $Wsubsetsubset X$.
Let $f_k:U_kto X$ be continuous, where $U_ksubset X$ is an open neighborhood of $K_k$ and $K_1:=overline W,;;K_{k+1}:=f_k(K_k)$.

Assume $V_ksubset X$ is open and $epsilon_k>0$ are such that
begin{align*}
(i)&;V_ksubset U_k\
(ii)&;operatorname{diam} V_k<epsilon_k\
(iii)&;V_kcap K_kneqemptyset\
(iv)&;|f_k-operatorname{id}|_{K_ksetminus V_k}<epsilon_k;.
end{align*}

Assume $F_k:=f_kcirccdotscirc f_1:Wto X$ converges uniformly on compact subsets of $W$ to some $F:Wto X$.
Then,

if $xin K_k$, $$ d(x,F(W))le2cdotsum_{nge k}epsilon_n $$
holds true.

(the notion of distance follows from the topology of $X$ or from some Riemannian metric, which induces the same topology).

If $xin K_k$, one can easily see (by splitting the cases $xin K_kcap V_k$ and $xin K_ksetminus V_k$ and exploiting $(i)-(iv)$) that
$$
d(x,K_{k+m+1})le2cdotsum_{n=k}^mepsilon_n
$$

hence it seems a reasonable result but I’m having some hard times proving it, in fact we can’t just pass to the limit in this last one, since
$$
K_{k+m+1}=F_{k+m}(overline W)
$$

and ${F_k}$ converges on compacts of $W$, not on $overline W$.

I tried to consider ${C_j}_j$, a (almost) normal exhaustion of $W$, that is
$$
C_jsubset W;;mbox{compact},;;C_jsubsetneq C_{j+1},;;bigcup_jC_j=W
$$

(the usual notion of normal exhaustion involves $C_jsubsetoperatorname{int}C_{j+1}$, but $W$ itself could have empty interior, hence this notion would be void; and what I wrote seems to be reasonable); assuming that such a sequence exists (it doesn’t look like a problem!), for every $epsilon>0;exists j_{epsilon}$ such that, setting $K_k^{(j)}:=F_{k-1}(C_j)$, one has
$$
d(x,K_k^{(j)})<epsilon;;forall jge j_{epsilon};.
$$

Let $yin C_j$ realizing the minimum:
$$
d(x,K_k^{(j)})=d(x,F_{k-1}(y)).
$$

then
begin{align*}
d(x,F(W))
&le d(x,F(C_j))\
&le d(x,F(y))\
&le d(x,F_{k-1}(y))+sum_{nge k-1}d(F_{n+1}(y),F_n(y))
end{align*}

the last sum is finite because of normal convergence, but I don’t see how make it depend on the $epsilon_k$, for example, to get some control on it.

My goal is to prove that what happens at step $k$ is not modified too much when we keep going. For example if we fix $alpha>0$ small and define $epsilon_k:=alpha/2^{k+1}$ we would get that $x$ doesn’t move more than $alpha$ from the image $F(W)$.

Any hint? Do you think it’s a false statement (so I should modify the hypotesis a bit)?. Thank you very much

design – Steps to achieve the best code quality?

Currently I’m on a project where my objective is to obtain the best code quality.

I have unit, integration & e2e testing. The e2e tests are written with the Business team in cucumber and is the final condition to achieve the end of a user history. Also, all feature must have the metrics that we want to change. I added mutation tests also, and we have near 100% of the mutants killed (so, we also have 100% of coverage).

We established the most restrictive rules in Sonar, and all our code has A grade. We also have stress tests and we are confortable with the results.

I’m really proud of this project, and yes, having this level of quality really helps to have security of our product, and now we develop faster than ever.

So… what is the next step to achieve über-quality?

Sorry for my english, is not my native language.

url rewriting – Bilingual WP site: How to achieve different URL sturcture rule based on its language?

A wordpress website with bilingual setup, with three languages:

  • English (“primary language”) : “en”
  • Traditional Chinese : “zh-hant”
  • Simplified Chinese : “zh-hans”

The bilingual setup is currently achieved with the polylang plugin.

I would like to have different URL structure based on the post’s language.

For English,

example.com/blog/%category%/%post_title%

But for non-English blog post URL, I would like to use post id instead of post_title:

example.com/blog/zh-hant/%category%/%post_id%

magento2.4.1 – How to achieve set Google Business Reviews Authorization Bearer Token?

I am using Google Business Review API, every time i manually copy and paste the Bearer token, how to achieve this?,

My code :

<?php
namespace ZeroStorelocatorBlock;

class Customerreview extends MagentoFrameworkViewElementTemplate
{
public function __construct(
AmastyStorelocatorBlockviewAttributes $attributes,
MagentoFrameworkViewElementTemplateContext $context,
MagentoFrameworkAppConfigScopeConfigInterface $scopeConfig,
array $data = ()
) {
$this->_attributes = $attributes;
$this->scopeConfig = $scopeConfig;
parent::__construct($context, $data);
}

protected function _prepareLayout()
{
parent::_prepareLayout();

}

public function getCustomerReview(){

//Here you need to check system.xml file and get section_id/group_id/field_id
$authentication = $this->scopeConfig->getValue('customerreview/general/review_auth', MagentoStoreModelScopeInterface::SCOPE_STORE);

$accountId = $this->scopeConfig->getValue('customerreview/general/review_account_id', MagentoStoreModelScopeInterface::SCOPE_STORE);

$authorization = "Authorization:".$authentication;
$url = 'https://mybusiness.googleapis.com/v4/accounts/'.$accountId.'/locations/45545454545454545454545/reviews';

$ch = curl_init();
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json' , $authorization ));
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_TIMEOUT, 10);
$output = curl_exec($ch);

$writer = new ZendLogWriterStream(BP . '/var/log/output.log');
$logger = new ZendLogLogger();
$logger->addWriter($writer);
$logger->info(print_r('line no:42'. $output, true));

$allData = json_decode($output, TRUE); // You will get all the data

return $allData;

}

public function getCustomAttributes()
{
if ($this->_attributes->getLocationAttributes()) {
return '';
}
return $this->_attributes->getLocationAttributes();
}
}

Guest Post: How to Achieve More in the Post-Pandemic World by Dustin B. Cisneros, CEO of RackNerd

Continuing our Opinion-Editorial and Guest Article Series, today we are talking about how to achieve more, in a post-pandemic world. Seeing that some members of LEB community are business owners or entrepreneurs themselves, we hope that you’ll be able to take home some valuable insights from this article.

RELATED: Dustin has previously shared the following business tips with the LowEndBox community, worth reading and checking out as well:

Looking for a VPS for your business or your idea? Be sure to check out RackNerd’s exclusive LEB offers by clicking here.

Seeing that we are all now transitioning into a post-pandemic world, today I wanted to share some tips with you on how you can achieve and do more. Perhaps you’re an individual who aspires to do more, or perhaps you’re a business owner. Whatever your current situation may be, here are some key points to keep in mind:

  1. OMNIPRESENCE IS KEY: What does this mean? This means that your brand should be everywhere, widespread, and constantly encountered. If people don’t know about your brand… they can’t transact with you. You may not earn business immediately, though it’s important to realize customers often sign up after seeing several marketing campaigns. As an example, RackNerd is actively engaged with the web hosting communities, as well as multiple social media platforms. Are you engaging with your client base, on the platforms they are engaged on? Technology makes it possible to be omnipresent today, so use this to your advantage!

    Perhaps this translates to more ads, even though it may not necessarily generate an instant or desirable “return on investment” – it will at the very least, get your brand more well known to your audience. 
  2. DON’T BE PASSIVE: Look, the reality is – your business does not run on auto-pilot, and customers can always appreciate the value of being with a company that has active management, and an owner that is truly involved and cares about customers. I am a firm believer that there are always certain things that you should never delegate as a business owner, remembering that no job is beneath you, and the willingness to adapt into different roles and communicate with customers is important. 
  3. STEP ON THE GAS: Action wins, every time. How many times have you ever told yourself, “I’ll work on it soon, “I’ll do it tomorrow,” or “I’ll get to it on Monday”? Let’s face it: when you say that to yourself, you are only deprioritizing that one thing or action.

    I challenge you to operate with urgency going forward — do it NOW rather than later, and start creating and building your brand. Try this and go tackle all the pending tasks you have on your to-do list today instead of putting it off to another day – I promise you’ll feel more confident, ready to take on and do more! 
  4. FIGURE OUT WHAT GETS YOU EXCITED: The lack of motivation, or what some people refer to as “burn out” – is actually caused due to a lack of purpose, passion, and initiative.

    You need purpose, both personally and professionally. 

    For me, it’s about focusing on the correct target – and the desire to expand in every area of my life; both personally and also in my business. I never want to condense or scale down, and this understanding of expansion truly inspires me, because I know that any organization or business can only save or scale down so much – while on the other hand, the potential to grow, expand, and create more is unlimited (which is why I’d much rather have attention of expansion and growth). 

    I’d like to think that my company, RackNerd, has been an exemplary example of this – we started off in one datacenter location and have since grown to 17+ global datacenter locations, and have experienced tremendous growth even throughout a pandemic. While others were focused on figuring out ways to shrink down, in the middle of a pandemic we went out and had our full attention on expansion – more product lines, more datacenter locations, obtaining real office space here in Southern California, hiring more staff, introducing more features to existing customers, and so much more. We have more exciting news, related to additional expansion to share soon with the community! 

  5. CONFRONT SALES: Realize that your business is a sales organization, and it requires sales and customers in order for it to be a business. In the process of figuring out how to scale your sales team and sales presence, it’s also important for you as a business owner to not lose focus on everything else that matters (operations, support, customer experience, etc). What do you need to do to achieve that? Perhaps it might mean onboarding more staff, or perhaps it might mean more involvement from you as a business owner. Operate with full and complete certainty, as doubt slows down any organization. Take a look at my last guest post here where I talk more in depth about how to sell, what to sell, and what to avoid selling.

Thank you for taking the time to read, I truly hope that this helps point you towards the right direction, or at the very least inspires you to achieve and do more with your business! I am a strong believer that one’s own potential is truly unlimited, even though he or she may not have fully realized it yet. Hard work and helping others has always been characteristics that inspire me a lot, so if at least one fellow community member here can improve their condition by reading this, then I’m glad to have made a positive impact by sharing this post.

I’m always open to connecting with other like minded individuals or business owners. Feel free to connect with me on LinkedIn, or e-mail me at dustin@racknerd.com if you have any questions or if you’d just like to say hello. Also, feel free to comment below if you have any additional feedback, or ways you’ve found work in helping achieve more!


Editor’s Note: The opinions and context above are solely those of the author, Dustin B. Cisneros of RackNerd, and do not represent the opinions, values, or positions of LowEndBox. If you or your company would like to take part in a Guest Post, Interview, or Op-Ed, please contact the LowEndBox administration on our Help Desk.

raindog308

I’m Andrew, techno polymath and long-time LowEndTalk community Moderator. My technical interests include all things Unix, perl, python, shell scripting, and relational database systems. I enjoy writing technical articles here on LowEndBox to help people get more out of their VPSes.

What preset can be used to achieve similar desaturated colors?

What preset can be used to achieve similar desaturated colors as Andrea Dabene? Props if I can also take these pics SOOC (film simulations) on a Fuji. Example pics here.

https://www.instagram.com/p/CO2qHtMreWn/

https://www.instagram.com/p/COg568oLvSK/

https://www.instagram.com/p/CObhl7Cr9JZ/

https://www.instagram.com/p/COJjQsjrxF4/

https://www.instagram.com/p/CNxIAmfrSEc/

https://www.instagram.com/p/CNeAs-ALQOO/

https://www.instagram.com/p/CMV4tN5rKSk/

Instagram for more pics: https://www.instagram.com/andreadabene/?hl=en

Manually Complete All in One SEO Basic Backlinks to Achieve Google First Page Ranking for $6

Manually Complete All in One SEO Basic Backlinks to Achieve Google First Page Ranking

Welcome to my All in One SEO Basic.
After too many attempts I have finally found the desire answer to what needs to happen to help your website rank up, so make sure you do not miss out:-

You are now on a Service that will provide you with a High Quality Manual All in One SEO Package that is designed to work across all websites.

This All in one SEO package will move you higher on the search engine (Google, Yahoo, Bing and Others) rankings, which in turn will provide you with FREE organic traffic to your site via highly targeted visitors and you will get more Sales.

Total: I will give you total 80 High Authority DA (Domain Authority) 80+ Real Human SEO Backlinks that exactly Worked. We don’t Offer Here Thousands of Backlinks Because this service is very Effective and Manual. I hope you will get your result in 7-15 days timeframe.

Using my Favorite and Tested strategy I can provide you with the following:

  • Increased website authority (High DA, PA, TF and CF)
  • Top-ranked website based on keywords
  • Manually built backlinks with only high DA site, using unique content
  • Google Penguin 3.0 & Panda 4.1 safe work (No Risk)
  • 100% White Hat and manual work only (we work with our team)
  • Flawless Approval rate
  • Do-follow backlinks
  • Only a Natural mix of anchored and non-anchored high DA backlinks

Why I choose This Strategy: All in one SEO Package is a Full Off Page Capsule also All in one SEO Strategy is the most popular Method to get Google 1st page Ranking with Targeted Keywords.

Required information’s to get started your Order:

  1. Website URL (1 is good for 1 order)
  2. Keywords (Maximum-5) Please Put Your keywords one per line
  3. A Targeted Title- 50-80 Character.
  4. Unique Article (400-1200 Words).Note: For Non-English Website – You need to send a 300-1000 words a unique article must, you can send also a spun/spin format article (Your website language article). I don’t create any non-English articles. You must send Please.. You can select multi-quantity scrape unique articles from Extras.
  5. Image (optional): You can send your image URL/Link.
  6. Video (optional): You can send your any Video URL/Link.
  7. Audio (optional): You can send your any audio URL/Link.
  8. Your Business/Website Category/Niche(optional)

Special Offer for Limited Time:

Buy 5, you will get free 1

Buy 10, you will get free 2

Buy 15, you will get free 3

Buy 20, you will get free 5

Buy more from EXTRA’s as Tier 2 Backlinks that will maximum chance To Get Google TOP RANKING.

FAQ (Frequently Asked Question):-
1. Are these links Do-follow?

Ans: Most links are do-follow and few links no-follow, contextual, anchored, and non-anchored to look more natural on Google’s Eye when the links are indexed.

2.How many URL’s and keywords am I allowed?

We need one URL and up to 5 keywords for this package for Single Order.

3. Are these backlinks Google safe.

Ans: These links are 100% Google Panda, Penguin, and Hummingbird safe. Most of the backlinks will be created from high quality & authority trusted sites with zero outbound links, Old domains, Contextual and relevant Unique Content so No RISK.

4. Do you accept all Niche Websites?

Ans: Yes, I accept all niches websites like BUT No Adult & Alcohol please!

5. Do you accept all Language’s Website of the World?

Ans: YES, I accept all the languages website of the world.

6.Do you offer a guarantee ranking?

Ans: No, We not offering Google First Page Ranking but You will see your Website’s Ranking Improvement (Guarantee)


7. Are you Really Work Manually???


Ans: Oh! Most Popular and Most Asked Question I ever Listen. First you need to understand many of Guys Offering Here Thousands of Backlinks in 1 or 5. You won’t Believe, to making 30 SEO manual Backlinks It takes 1 Hour Plus time. So I give you 100% Guarantee not only my this service but also my all services are Manual Service. I work with my SEO Team.

To increase Your Links indexing chance, do not forget to order Tier 2 Backlinks from The Extra and must use Scraped unique articles from Extra.

Don’t Forget to Check our other SEO Services to get More Link Juice.

Note: To Avoid order cancellation kindly contact with us…Thanks

.(tagsToTranslate)all(t)manual(t)backlink(t)allseo(t)edu(t)google

How can I achieve this "stroked lines" effect?

I have a collection of arbitrary vertices and edges that are connected, but which do not necessarily form triangles. These vertices and edges mostly fall along a flat plane, but have some height variation. They represent points on terrain with paths between them. You could think of them as overlapping roads, rivers, or a navigation mesh.

I would like to draw them like this:

lines drawn in the way I want

In this sketch, we see vertices (black) and edges(red) on the left. On the right, we see blue blobs corresponding to the pixels that are within some radius "r" of any vertex or edge. When viewed from the side, the blue areas basically correspond to the "floor" near edges and vertexes.

So far, I have tried to do this just by drawing quads at along every edge with thickness "r", but this doesn’t look great because the vertices are all (slightly different) heights (they are positioned on terrain), so the individual quads are visible as overlapping ribbons in 3d space. I also haven’t yet looked into how to make smooth miters where the vertices join multiple edges together.

I should mention I’m using glsl for this project, and modern opengl on android.

One thing I’ve though of doing was "painting" a 2d raster image as viewed from above, and then projecting that down onto the terrain as a texture or light of some kind.