postgresql – Query slowing down the database performance

I have a query to find the maximum spacing between charging stations on any given route, using PostGIS and pgRouting. An example query is below:

select (max(delr) * st_length(line::geography) * 0.000621371) as max_spacing
from (select sq2.ratio - coalesce((lag(sq2.ratio) over ( order by sq2.ratio)), 0) as delr, line
      from (select ST_LineLocatePoint(line, sqa.points) as ratio, line
            from sp_od_ferry(98282, 98002) as line,
                 (select st_setsrid(st_makepoint(longitude, latitude), 4326) as points
                  from (select longitude,
                               latitude
                        from evses_now
                        where analysis_id = 565
                          and (connector_code = 1
                           or connector_code = 3)
                        union
                        select longitude,
                            latitude
                        from zipcode_record
                        where zip = '98282'
                           or zip = '98002') as sq) as sqa
            where st_dwithin(line::geography, sqa.points::geography, 16093.4)
            order by ratio asc) as sq2) as sq3
group by sq3.line;

Briefly, the logic is to find the points (charging stations) near the shortest path (given by user-defined function sp_od_ferry()) between origin and destination and find the length of the longest segment between points.

I have to run the above query for several OD pairs, and several of these calculations can be launched in parallel by users. I used AWS RDS performance insights and it found the above query to be the slowest one and causing database slowdown (and 100% CPU usage on the DB instance).

enter image description here

On EXPLAIN ANALYZE, it shows the nested inner loop to be the costliest step. https://explain.dalibo.com/plan/jTf

enter image description here

I understand one way to reduce the database load would be to provision a bigger RDS instance. I currently use (db.t3.small) which has the following specs:

enter image description here

I used pgTune to make the changes to the default AWS RDS Postgres 12.5 settings. The new config is below:

max_connections = 100
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2621kB
min_wal_size = 2GB
max_wal_size = 8GB

Any suggestions regarding the query or ideas about how I can manage the database load while keeping the costs low are appreciated.

wp query – Getting list of categories that have posts in search results

Looking for a solution for the following problem.

I am trying to create a list of categories that contain posts in a results page. Ideally this would also contain a post count.

Up to this point I would run a query using 'post_per_page'=>-1', 'fields'->'ids' only, then using a loop I would use the get_the_category(), get_ancestors() and populate an array along with counts. Up to recently this used to be working nicely, however, the website is growing a lot larger by the day, so this is not feasible any more since it takes a lot of time. I knew in the beginning that at some point this would come to a stall, however, this came earlier than I was expecting.

From what I realized, using the whole WP_Object for each post is too heavy for the server if the results exceed 2k in count, but on the other hand only using 'fields->'ids' forces me to use loops – which is also heavy.

One solution I ended up with, is limiting results to the first 1000, but obviously this would mean, that the counts in the categories will not be accurate, and some categories might be left out, if they exist in results that exist after the first thousand.

I am willing to sacrifice the counts as long as I am able to populate a complete category list.

Has anyone done anything similar? Looking forward to your replies.

INNER JOIN taking so long for even simple query

My inner queries when executed independently works fast(<2 sec). I have then applied INNER JOIN on two sub query results and final output takes around 25 minutes.

SELECT DISTINCT
tableA.consumer,
tableA.ltm,
tableA.consumer_dpip,
tableA.consumer_dpname,
tableA.consumer_port,
tableB.provider,
tableB.provider_DPIP,
tableB.provider_DPNAME,
CASE when instr(tableB.provider_DPIP, tableA.consumer_dpip) = 0
then  tableB.provider_port
else null end ,  
tableB.endpoint,
tableB.endpoint_port    
FROM
(
select DISTINCT
sender.consumername   AS consumer,
devices.ltmip         AS ltm,
devices.dpip          AS consumer_dpip,
devices.dpname        AS consumer_dpname,
appdevices.port       AS consumer_port
from
vz.t_vz_mapnames                  mapnames,
vz.t_vz_sender                    sender,
vz.t_vz_application_devices       appdevices,
vz.t_vz_device_info               devices  
WHERE
mapnames.sno = sender.parentid
AND appdevices.appid = sender.consumername
AND upper(appdevices.zone) = devices.zone
 AND mapnames.type = 'consumerRoutingTable'
AND appdevices.type = 'Consumer'
AND devices.env = 'Production'
) tableA
inner join
(
select  sender.consumername   AS consumer,
sender.providername   AS provider,
LISTAGG(distinct(devices.dpip), ',') WITHIN GROUP(order by devices.dpip) as provider_DPIP,
LISTAGG(distinct(devices.dpname), ',') WITHIN GROUP(order by devices.dpname) as provider_DPNAME,
appdevices.port as provider_port,
dest.url              AS endpoint,
dest.port             AS endpoint_port
from
vz.t_vz_mapnames                  mapnames,
vz.t_vz_sender                    sender,
vz.t_vz_application_devices       appdevices,
vz.t_vz_device_info               devices,
 vz.t_vz_ep_destination_template   dest
WHERE
mapnames.sno = sender.parentid
AND appdevices.appid = sender.providername
AND upper(appdevices.zone) = devices.zone
AND dest.applicationid = sender.providername
AND mapnames.type = 'consumerRoutingTable'
AND appdevices.type = 'Provider'
AND devices.env = 'Production'
AND dest.env = 'PROD'
 group by sender.consumername,sender.providername,appdevices.port, dest.url, dest.port
)tableB 
on tablea.consumer=tableb.consumer  and tableB.endpoint is not null and tableB.endpoint<>' '
order by tableA.consumer, tableA.ltm

Am I doing anything wrong here or do I need compound indexes added ? Any inputs highly appreciated.

wp query – Get user first name from custom endpoint

I am trying to set up a custom endpoint which only returns some user data instead of the default data sent back by the users endpoint. But I don’t know how to get the first and last name of the user from WP_User_Query

add_action( 'rest_api_init', function () {
  register_rest_route( 'myplugin/v1', 'some-endpoint', array(
    'methods' => 'GET',
    'callback' => 'my_awesome_func',
  ) );
} );

function my_awesome_func( $request) {

            $user_fields = array( 'user_nicename', 'user_url', 'first_name' );
            $wp_user_query = new WP_User_Query( array( 'role' => 'subscriber', 'fields' => $user_fields ) );
            $members = $wp_user_query->get_results();

    return new WP_REST_Response(
        array(
            'status' => 200,
            'response' => $members
        )
    );
    
    return $response;
}

If I remove ‘first_name’ from my array then I get back user_nicename and user_url in the response for all users. But with first_name in there I just get an empty response. I also tried firstname instead of first_name but still no joy.

Also, this only returns a handful of users. How do I show them all or 10, 20, 30 etc.

Are there any generic Abstract Syntax Tree Query Languages (specification)?

While designing compiler specifications I noticed that having some sort of language to further validate and manipulate the AST’s gotten from a Parser would be useful in separating concerns. The overarching goal is to create a maintainable language stack specification. So far I have worked out generic lexer and parser phases: instead of a defined ANTLR ruleset I have a specification for any ruleset. I wish to do the same for further validation of the AST’s (double variable name declarations, non-existing variables etc). While not a hard requirement it would be excellent if this tool is also able to manipulate the AST, similar to database queries.

I went looking for existing query languages or AST manipulation syntax and found the following: TreeQL, SPARQL, Neo4j, SBQL, JSONIQ, CRAQL, astquery.io, AST Query Engine, Tregex & Tsurgeon (still inquiring into these two) and SMARTS.
JSONIQ looked promising but would bind the specification to representing AST’s as JSON. The other options either do not support manipulation or are otherwise too restrictive.
I’d be happy to hear if someone can point me to a Tree/AST manipulation tool/query language (or even better, specification). Thanks in advance.

jquery – Replace Source query string value without redirecting

If you are working with browsers that support the HTML5 History API, you could use replaceState:

$(document).ready(() => {
    var redirectUrl = _spPageContextInfo.webAbsoluteUrl + "/" + _spPageContextInfo.layoutsUrl + "/Redirect.aspx";

    var queryParameters = {}, queryString = location.search.substring(1), re = /((^&=)+)=((^&)*)/g, m;

    while (m = re.exec(queryString)) {
        queryParameters(decodeURIComponent(m(1))) = decodeURIComponent(m(2));
    }

    queryParameters('Source') = redirectUrl;

    var updatedParams = location.search.replace(location.search, $.param(queryParameters));

    if (history.replaceState) {
        var newurl = window.location.origin + window.location.pathname + "?" + updatedParams;
        window.history.replaceState({ path: newurl }, '', newurl);
    }
});

However, if you are trying to modify the source on a form in SharePoint, that won’t be sufficient. Some onclick events are populated when the page loads with the initial Source value and these events will have to be override as well since they will redirect to the previous value:

$("#part1").on("click", "input(value='Cancel')", function (e) {
    e.preventDefault();
    window.location.href = redirectUrl;
});

$("#Ribbon.ListForm.Edit.Commit.Cancel-Large").click(function (e) {
    e.preventDefault();
    window.location.href = redirectUrl;
});

mysql – Unable to store query result inside a variable

I am trying to query user by email and save the result inside a variable. I tried using await and async, callback and promise but none of them actually work.

const checkEmailUsed = (email) => {
  let sql = "SELECT * FROM users_signup WHERE user_email = ? LIMIT 1";
  return new Promise((resolve, reject) => {
    conn.query(sql, email, (err, res) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(res(0));
    });
    conn.end();
  });
};

var user = ();
checkEmailUsed("mikias@email.com")
  .then((res) => user.push(res))
  .catch((err) => console.log(err));

console.log(user); // Still ()

wp query – Changing wp_head to a different post/page

The problem in short: I need to be able to replicate the experience of a post on a static page, including title and meta tags. To do this, I need to set the pages post data to the post I’m cloning. However, when I call setup_postdata before get_header, I still get the head info from the original page.

Longer: So, the reason I’m doing this is, a client wants to have a custom url structure that doesn’t include a slug or ID, but a meta value. I was able to pass these meta values to a page, but obviously still need to run the query that will pull the correct post info. I can clone the entire post, except for the dang wp_head. Is there a way to set the wp_head to use the post information from another post/page?

I’ve attempted to hook into wp_head and alter the global wp_query AND post variables, but no luck. Will the easiest way to do this be to manually recreate the head on that page not using wp_head?

sql server – Check if contract conflict with another contract query

I have a database with these tables as the bellow picture showed:

enter image description here

The user can create a new contract each contract may have one or more shops and these shops status changes depending on the contracts, everything works perfectly until now.
my problem in renewing the contract, I have a feature that allows the user to renew a contract, in this case, I get all the original contract info and make the new contract start from the end date of the original contract + 1 day with the same original contract info.

my question here is how to check if this renewal contract does not conflict with other contracts for the same customer and the same shops (customer can have many contracts)

E.G.:

I have contracts like this:

con_id    shop_id   start_date    end_date
--------------------------------------------
  1         1       14-04-2021    14-04-2022
  2         1       15-04-2022    15-04-2023

If the user clicked on contract number 1 and tried to renew it, I want a query to check if the new contract start date does not conflict with another contract for this user and these shops.

Like in my above data I want to prevent the user from renewing contract number 1 again because there is already a contract renewed before in that period.

This is what I tried:

--IF EXISTS (SELECT * FROM contracts_view where cust_id=123456789 and @date_start >= date_start and @date_start <= date_end and shop_id in (select shop_id from contracts_shops where contract_id =@old_contract_id)) 
--BEGIN
--    SELECT @ErrorMessage = ERROR_MESSAGE()
--  RAISERROR ('asdasd', 10, 1)
--  ROLLBACK TRAN
--   return
--END

and here is my stored procedure for renewing a contract:

ALTER PROCEDURE (dbo).(contract_renew)
-- Add the parameters for the stored procedure here
@cust_id int,
@duration int,
@price decimal(10,2),
@tax decimal(10,2),
@usage nvarchar(20),
@rent_type nvarchar(10),
@price2 decimal(10,2),
@note2 nvarchar(max),
@date_start date,
@date_end date,
@note nvarchar(max),
@app_user nvarchar(20),
@old_contract_id int
AS
BEGIN

DECLARE @ErrorMessage NVARCHAR(MAX)
DECLARE @ID int

BEGIN TRAN
BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--insert data
INSERT INTO (dbo).(contracts)
       ((cust_id)
       ,(duration)
       ,(price)
       ,(tax)
       ,(usage)
       ,(rent_type)
       ,(price2)
       ,(note2)
       ,(date_start)
       ,(date_end)
       ,(note)
       ,(app_user))
 VALUES
       (@cust_id,
       @duration,
       @price,
       @tax,
       @usage,
       @rent_type,
       @price2,
       @note2,
       @date_start,
       @date_end,
       @note,
       @app_user) SELECT SCOPE_IDENTITY();

       SET @ID = SCOPE_IDENTITY();


insert into contracts_shops (contract_id, shop_id)
select @ID, shop_id
FROM contracts_shops WHERE contract_id = @old_contract_id;

COMMIT

END TRY
BEGIN CATCH
    SELECT @ErrorMessage = ERROR_MESSAGE()
    RAISERROR (@ErrorMessage, 10, 1)
    ROLLBACK TRAN
END CATCH
END

mysql – How to run multiple calculations from multiple tables in one query,

I have a school management system that stores student marks and generates student report

For a student to pass he she has to have ;

  1. An Average of 60% or above
  2. Get 60% or above in English Language
  3. Get at least 60 % in 5 subjects including English Language

I do have the query to calculate the best 5 subjects and comes up with an average.

But I need my query to be able to check the value of the passing subject and also does a count of the number of subjects a student has passed (inclusive of English) and presents that info in one query

SELECT student_id, round((SUM(t.mark))/5) average_mark from (
            select marks.student_id,  ROUND(AVG(mark)) as mark  from marks
                INNER JOIN teaching_loads ON teaching_loads.id=marks.teaching_load_id
                INNER JOIN subjects ON subjects.id=teaching_loads.subject_id
            where marks.student_id = "520" AND marks.assessement_id=1  
            GROUP BY subject_id
            order by (subject_id =2) desc, mark desc
            
            LIMIT 5
            
            )t ORDER BY round((SUM(t.mark))/5) DESC

How can I build a query that gets checks the value of the passing subject and also does a count of the number of subjects a student has passed (inclusive of English) and presents that info in one query

Something like

Student_id:89
passed_subjects:6
passing_subject_mark:60

In one query I want to be able to get all that data, how can I go about it,
Please help me.

Below is the schema for the databse that stores student data/marks and its related table.

Marks Table-Stores student marks

CREATE TABLE `marks` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `teacher_id` bigint(20) UNSIGNED NOT NULL,
  `student_id` bigint(20) UNSIGNED NOT NULL,
  `teaching_load_id` bigint(20) UNSIGNED NOT NULL,
  `assessement_id` bigint(20) UNSIGNED NOT NULL,
  `mark` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Teaching Loads

CREATE TABLE `teaching_loads` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `teacher_id` bigint(20) UNSIGNED NOT NULL,
  `subject_id` bigint(20) UNSIGNED NOT NULL,
  `class_id` bigint(20) UNSIGNED NOT NULL,
  `session_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Subjects Table

CREATE TABLE `subjects` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `subject_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject_type` enum('core','elective','non-value','passing_subject') COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SQL Fiddle that has the database schema