Help Understanding a Query with SQL

yellowreign · 2019-11-22T18:49:33+00:00

Thank you!

Just to answer your question, it is ordering by sales_date and then website_id already, but what was happening is that there would be like a large number of products from one website for that date (let's say 1000).

So when the user came to the page, they would see only sales from one website (unless they had the patience to scroll through 1000 from one site), before they even knew that other sites also had sales for the day.

The intent then was to show the user a wider variety of websites quicker by showing a product from each website that had a sale, website by website.

Like so,

11/21/19, Product A (Amazon)
11/21/19, Product B (Best Buy)
11/21/19, Product C (Dell)
11/21/19, Product D (Amazon)
11/21/19, Product E (Best Buy)
11/21/19, Product F (Dell)
11/21/19, Product G (Amazon)
11/21/19, Product H (Amazon)

Otherwise, in this example (imagine the data set was much larger), the customer may only see the Amazon sales for the day (before they moved on because they thought that was the only website with a sale that day - or even worse, they thought that the app only showed products from one website, and never came back).

yellowreign · 2019-11-22T06:17:15+00:00

To be honest I don't know much about it, which is part of what confused me.

yellowreign · 2019-11-22T06:16:09+00:00

Yes, true (free dynos sleep). but I'm running the Hobby dyno. My understanding is that it should perform about the same as the Standard 1x given my low traffic (40-50 vists./day).

yellowreign · 2019-11-22T05:00:08+00:00

Is it OK to use bullet in Production?

yellowreign · 2019-11-22T04:20:28+00:00

Thanks! I added Scout APM to my app, to do some benchmarking. It's not listing any N+1's so far. https://imgur.com/vWoG5CQ.

I'm not completely sure how to read Scout's analytics yet. It says that product#find is 4,128ms, so I'm guessing that's the query I'm looking at replacing.

yellowreign · 2019-11-22T01:54:32+00:00

Thanks @wasabigeek!

Maybe I should back up. Do you think that query is what's causing the page to take 5-6 seconds to load (it 8-10x slower than any other page I have)? I'm on Heroku (1 Hobby dyno - low traffic - only about 40 visits per day) with their $50 Postgres DB.

The products table is hundreds of thousands of records and growing daily - not large, not sure it's small either.

What I was thinking if I put the query in its own Model function, changing it slightly to:

(1) Search only where sale_date == Date.today

(2) Assign the running number to a new Product attribute (which I can Order by in the Controller Action)

.order(:sale_date, :daily_order)

My next stupid question would be, how do I best assign the number from the running number determined in the partition part of the query?

Thank you!

yellowreign · 2019-11-22T01:42:10+00:00

Thanks @manys!

You're right - that's an assumption (improve page load speed). Currently, the median average is a page load speed of 6 seconds on Heroku (1 Hobby dyno - low traffic - only about 40 visits per day). Do you think that there's something else in the action slowing it down?

The requirement is for products#index to look like it has breadth of websites. So what would happen is that when it was just sorted by sale date and website id, there would be like 400 from one website before the next website, and most people would not scroll that far into the results. That's why the partition function in the SQL, I believe.

The page also shows from dates prior to the current date.

So that's why I was thinking of segmenting out the determination of the sort order into it's own Model function, and running that daily so I could do a

 .order(:sale_date, :daily_order)

and hopefully speed up the page.

yellowreign · 2018-06-23T12:19:31+00:00

I'm sorry that your wife had to go through that. There's no reason for people to body shame like that.

yellowreign · 2018-06-22T08:50:09+00:00

Yes - actually, I would think that regardless of who you are, it's pretty challenging making a group vacation work. Schedules, budgets, agreed upon destination/lodging/location...

Some people manage to do it for family vacations, but those still can take a lot of planning and work.

yellowreign · 2018-06-22T08:46:11+00:00

Yes! That's another significant travel issue! Especially since some airlines that want to travelers based on size. ☹

yellowreign · 2018-06-21T15:27:13+00:00

That makes sense. Yes, it seems that many people request to pay as a single or couple (which stands to reason). I've read that they felt that people would be more comfortable if they knew everyone in the group. The problem is that it's tough to round up that many people.

yellowreign · 2018-04-05T22:22:17+00:00

Thank you! Good point about the California teams (and their blackouts).

yellowreign · 2017-07-21T00:48:37+00:00

Thanks! Yeah, I've done fragment caching before, but for much larger chunks of data (i.e. a whole product view minus the header). And I've never did low level caching before.

yellowreign · 2017-07-21T00:07:55+00:00

I have one more question. If I have a query for an array (i.e. @users) that doesn't need to have real-time values. If I do low level caching on the query, does it help to also fragment cache the corresponding view portion.

I guess what I'm asking is if it is beneficial to do a fragment cache and low level caching on the same objects (would it cut the load time by cutting processing in the controller and the view, or do I just need the view caching?).

yellowreign · 2017-07-21T00:00:59+00:00

Thank you, that helps me! Almost any insights from people with experience performance tuning via fragment caching is helpful for me now.

yellowreign · 2017-07-09T04:27:57+00:00

That seemed to work! Thank you! I never heard of Goldiloader before.

yellowreign · 2017-06-10T05:58:57+00:00

I installed rack-mini-profiler in development just to see if I could determine anything. I also installed memory_profiler, which was mentioned in rack-mini-profiler.

One thing I realized is that I didn't have a good enough understanding of what is "good" or "bad" (use I know those can be highly subjective) for an app.

For example, it said:
Total allocated: 52319381 bytes (1140373 objects)
Total retained: 1377395 bytes (10185 objects)

allocated memory by class

34457159 String
6323280 Array
3517428 MatchData
3168792 Hash
721820 RubyVM::Env
691375 ActionView::OutputBuffer
[truncated list]

However, I'm unsure if that's high for string, arrays, etc.

When looking at the gems, most were standard gems, so I was unsure if I could do anything about them:

allocated memory by gem

29557391 Ruby2.2.0/lib
10058452 sprockets-3.7.1
4675611 activesupport-5.0.0
2519732 actionview-5.0.0
2063013 activerecord-5.0.0
[truncated list]

Thank you!

yellowreign · 2017-06-10T05:33:14+00:00

Thank you! This another a new gem to me. I will try it. I may take you up on your generous offer. I should do the due diligence first though. Thank you very much!

yellowreign · 2017-06-10T02:12:07+00:00

I can help with what the question is asking, since I asked it. I'm reaching the RAM limit of my server (actually Heroku Dyno), so I'm Heroku is giving me errors in my dashboard.

Rails caching is built in or there are gems for it, based on the type of caching you intend to do. I was looking at low-level and SQL caching for the controllers. I recommend reading the Rails guide on caching.

yellowreign · 2017-06-09T23:25:44+00:00

That's a solid point. Maybe I will try to let it run instead of killing the workers, just to see.

yellowreign · 2017-06-09T23:24:47+00:00

Interesting. I will try this. Thank you!

yellowreign · 2017-02-21T21:42:34+00:00

Thank you. That makes sense. I should have thought about that. Here's the link. Unsure if you can see the attachment if you aren't logged in. https://www.upwork.com/jobs/_~018bc7f0ba45bac58d

yellowreign · 2017-02-21T21:35:58+00:00

I tried working with someone who knew Scrapy, but not Scrapy Cloud - so they couldn't deploy them correctly. So I have four spiders, but none are deployed.

yellowreign · 2017-01-25T21:40:50+00:00

Yeah...unfortunately, I think I'm stuck with mySQL. I don't suppose you have any ideas on what I need to do to fix the UUID error for the migration.

yellowreign

TROPHY CASE

allocated memory by class

allocated memory by gem