sql server – SSMS – SQL Execution Plans

I am new to SQL execution plans and I’m trying to learn more about them.

I have a few questions.

How long does a SQL Execution Plan stay in memory? If I disconnect my connection, I assume it won’t be available next time? Is there any way to change this?
Can I export an Execution Plan and load it to memory the next time I reconnect to SQL Server to bypass the Query Optimizer and save time?

t sql – Do we have information about duration time of query in actual execution plan?

I am building application in C# which is parsing Actual Execution Plan. I need basic stats like Row count, CPU time, Page Reads etc. All above stats I found in XML of Actual Execution Plan. I can not find time duration of executed query. Do you know If it is possible to retrieve it ?

How can I reduce Estimated I/O and subtree cost in SQL Server Execution plan?

I have a table with more than 1 million rows.
When I execute a simple SELECT query it takes about 17 seconds to retrieve the data.

SELECT * FROM MyTable

The execution plan gives the following info:

enter image description here

Furthermore, I have 5 non-clustered indexes and one clustered index.

Any suggestion would be appreciated.

sql server – What is the best way to have actual execution plan and IO stats as values to insert?

We have idea to create procedure which will test performance of query and insert specific information associated with executed query to table with test performance history ((dbo).(TestHistory). We would like to collect:

  1. (ActualExecutionPlan) (XML) NOT NULL
  2. (LogicalReads) (BIGINT) NOT NULL
  3. (CPUTime) (BIGINT) NOT NULL
  4. (ElapsedTime) (BIGINT) NULL,
  5. (StartProcedureTime) (DATETIME2)(7) NOT NULL
  6. (EndProcedureTime) (DATETIME2)(7) NOT NULL
  7. (IndexNamesInDynamicSqlTableOrView) (VARCHAR)(8000)
  8. (RowCount) (INT) NOT NULL
  9. (ViewDefinition) (VARCHAR)(MAX) NULL

And insert these values after every execution of procedure with parameter.

So idea is to create procedure (dbo).(TestQuery) with parameter @sqlQuery varchar(max). @sqlQuery will be query to execute as dynamic sql with header -> SET STATISTICS XML,IO,TIME ON.

Question

Do you have idea how to get:

  1. (ActualExecutionPlan) as xml
  2. (LogicalReads)
  3. (CPUTime)
  4. (ElapsedTime)

As values to insert to (dbo).(TestHistory) table ? Is it any possibility to get it from “Messages” tab and result from SET STATISTICS XML ON ?

Execution failed for Task in Android Studio Execution failed for task ‘:app:dataBindingMergeDependencyArtifactsDebug’

I have the following Gradle file (build.gradle (app))

plugins {

    id 'com.android.application'
}

android {
    compileSdkVersion 30
    buildToolsVersion "28.0.3"

    defaultConfig {
        applicationId "com.example.bslhrms"
        minSdkVersion 16
        targetSdkVersion 30
        versionCode 1
        versionName "1.0"

        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }

    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
            manifestPlaceholders=(usesCleartextTraffic:"false")
        }
    }
    compileOptions {
        sourceCompatibility JavaVersion.VERSION_1_8
        targetCompatibility JavaVersion.VERSION_1_8
    }
    buildFeatures {
        viewBinding true
    }
}

dependencies {

    implementation 'com.android.support:support-core-utils-28.0.0'
    implementation 'androidx.appcompat:appcompat:1.3.0'
    implementation 'com.google.android.material:material:1.4.0'
    implementation 'androidx.constraintlayout:constraintlayout:2.0.4'
    implementation 'androidx.lifecycle:lifecycle-livedata-ktx:+'
    implementation 'androidx.lifecycle:lifecycle-viewmodel-ktx:+'
    implementation 'androidx.navigation:navigation-fragment:2.3.5'
    implementation 'androidx.navigation:navigation-ui:2.3.5'
    implementation 'androidx.legacy:legacy-support-v4:1.0.0'
    testImplementation 'junit:junit:4.13.2'
    androidTestImplementation 'androidx.test.ext:junit:1.1.3'
    androidTestImplementation 'androidx.test.espresso:espresso-core:3.4.0'


    implementation fileTree(dir: 'libs', include: ('*.jar'))

    implementation 'com.android.support:appcompat-v7-28.0.0'
    implementation 'com.android.support:design-28.0.0'
    implementation 'com.android.support:support-v4-28.0.0'

    // Glide image library
    implementation 'com.github.bumptech.glide:glide:3.7.0'

    androidTestCompile('com.android.support.test.espresso:espresso-core:2.2.2', {
        exclude group: 'com.android.support', module: 'support-annotations'
    })

    implementation 'androidx.cardview:cardview:1.0.0'
    implementation 'com.squareup.retrofit2:retrofit:2.5.0'
    implementation 'com.squareup.retrofit2:converter-gson:2.1.0'
}

Upon executing, i am getting the following errors

Execution failed for task ‘:app:dataBindingMergeDependencyArtifactsDebug’.

Could not resolve all files for configuration ‘:app:debugCompileClasspath’.
Could not find com.android.support:support-core-utils-28.0.0:.
Required by:
project :app
Could not find com.android.support:appcompat-v7-28.0.0:.
Required by:
project :app
Could not find com.android.support:design-28.0.0:.
Required by:
project :app
Could not find com.android.support:support-v4-28.0.0:.
Required by:
project :app

Possible solution:

Any help will be highly appreciated

Slow group by execution after update to MySQL 8.0.26

I am running a larger DB with tables > 200M datasets on a dedicated Ubuntu 20.04 machine.

Since a few days queries that use group by are extremly slow. I suspect that this is new since the last apt-get update of Ubuntu which might have updated MySql from 8.0.25 to 8.0.26 but I am not sure.

Current Version: mysql Ver 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Here is a sample query:

SELECT
  UNIX_TIMESTAMP(DATE) as time_sec,
  m.name AS POS,
  AVG(RATING)
FROM   merchants_product_ratings mpr
INNER JOIN merchants m ON mpr.merchant_id = m.id
INNER JOIN manufacturers_products p ON mpr.SKU = p.SKU
LEFT JOIN manufacturers_brands b ON p.brand_id = b.ID
WHERE
    MERCHANT_ID IN ( '1','2','4' )
  AND p.MANUFACTURER_ID = 19130
  AND FIND_IN_SET(mpr.MERCHANT_ID, '1,2,4')
  AND FIND_IN_SET(b.ID_PARENT, '14255,113,124')
  AND CASE WHEN 0 = 1 THEN p.focus=1 ELSE TRUE END -- show only focus PZNs? 
  AND DATE BETWEEN FROM_UNIXTIME(1625335554) AND FROM_UNIXTIME(1630519554)
GROUP BY DATE, mpr.MERCHANT_ID
ORDER BY 
    date, m.name

The query is unmodified and worked before in <1s

SQL Mode: `sql-mode=”STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,IGNORE_SPACE,NO_ENGINE_SUBSTITUTION”

Now I am wondering how to fix it as it was previsously running and if it is a result of a wrong SQL mode or wrong group by. There are several other examples of the same type where it also comes down to the fact that a query on each merchant alone runs < 100ms but once there are more to group by it slows down to > 30s.`

Python error inside worker for map_async effects execution plan

folks. I’ve faced this issue and I’m curious about what’s going on.
The Pool forks 3 processes. My further assumptions: each process will pull tasks from the parent task queue. As we see process didn’t die but some of the tasks are skipped. Maybe anyone has an ideas?

from multiprocessing import Pool
import os


def wrk(a):
    if a % 3 == 0:
        print(a, os.getpid(), 'GONNA DIE')
        raise ValueError('ERROR')
    else:
        print(a, os.getpid())


if __name__ == '__main__':
    with Pool(processes=3) as pool:
        p = pool.map_async(wrk, (i for i in range(50)))
        p.wait()

results

0 29836 GONNA DIE
5 29835
6 29835 GONNA DIE
10 29836
11 29836
12 29836 GONNA DIE
15 29836 GONNA DIE
20 29836
21 29836 GONNA DIE
25 29835
26 29835
27 29835 GONNA DIE
30 29836 GONNA DIE
35 29835
36 29835 GONNA DIE
40 29836
41 29836
42 29836 GONNA DIE
45 29835 GONNA DIE

Hovering over an Operator in Execution Plans: How to make the tooltip not disappear when moving the mouse

I want to share my screen and show an execution plan but when I hover my mouse over an operator and what to point to something with my mouse in the tooltip then it disappears. Is there a way to make a stay open while I point the mouse on some info on it?

Thanks

during execution it run and install successfully and open for less than a second and closes immediately [closed]

<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">

<ImageView
    android:id="@+id/imageView13"
    android:layout_width="0dp"
    android:layout_height="500dp"
    android:layout_marginTop="25dp"
    app:layout_constraintEnd_toEndOf="parent"
    app:layout_constraintHorizontal_bias="0.0"
    app:layout_constraintStart_toStartOf="parent"
    app:layout_constraintTop_toTopOf="parent"
    tools:srcCompat="@drawable/lady" />

<Button
    android:id="@+id/button3"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_marginStart="20dp"
    android:layout_marginLeft="20dp"
    android:text="@string/Back"
    app:layout_constraintBottom_toBottomOf="parent"
    app:layout_constraintEnd_toEndOf="parent"
    app:layout_constraintHorizontal_bias="0.0"
    app:layout_constraintStart_toStartOf="parent"
    app:layout_constraintTop_toBottomOf="@+id/imageView13"
    app:layout_constraintVertical_bias="0.753" />

<Button
    android:id="@+id/button5"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_marginEnd="20dp"
    android:layout_marginRight="20dp"
    android:onClick="nextImage"
    android:text="@string/Next"
    app:layout_constraintBottom_toBottomOf="@+id/button3"
    app:layout_constraintEnd_toEndOf="parent"
    app:layout_constraintHorizontal_bias="1.0"
    app:layout_constraintStart_toEndOf="@+id/button3"
    app:layout_constraintTop_toBottomOf="@+id/imageView13"
    app:layout_constraintVertical_bias="1.0" />

</androidx.constraintlayout.widget.ConstraintLayout>

Main Activity

package com.example.insta_layout;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.view.View;
import android.widget.ImageView;

public class MainActivity extends AppCompatActivity {
int counter=0;
ImageView img = (ImageView) findViewById(R.id.imageView13);

public void prevImage(View view){
    counter--;
    switch (counter){
        case 1: img.setImageResource(R.drawable.post1);
        case 2: img.setImageResource(R.drawable.post2);
        case 3: img.setImageResource(R.drawable.post3);
        case 4: img.setImageResource(R.drawable.post4);
        case 5: img.setImageResource(R.drawable.post5);
        default: img.setImageResource(R.drawable.lady);
    }
}
public void nextImage(View view){
    counter++;
    switch (counter){
        case 1: img.setImageResource(R.drawable.post1);
        case 2: img.setImageResource(R.drawable.post2);
        case 3: img.setImageResource(R.drawable.post3);
        case 4: img.setImageResource(R.drawable.post4);
        case 5: img.setImageResource(R.drawable.post5);
        default: img.setImageResource(R.drawable.lady);
    }
}

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
}

}

reinventing the wheel – Implementing conditinional execution in Python

I found abandoned project on GitHub and curious is it possible to implement part of it on just Python?

Goal:

  • Call functions only under some condition.
  • Track if ANY of inner function were triggered.
  • Reuse variables from the same scope to be more flexible with inner functions calls.

I have PoC working, but I wonder is any better algorithmic approach to do the same in Python? Most obvious way to do something similar to bellow. But is there more elegant way to get rid of repeating if ...: processed = True pattern?

def boo(a, c):
  # do something
  return True

def foo(a, b, c):
  # do something more
  return True

def setup(a, b, c):
   processed = False

   if ((a == "boo") & (c == 1)):
      if boo(a, c):
         processed = True

   if ((a == "boo") & ((c == 2) | (b == 3))):
      if foo(a, b, c):
         processed = True
   ...
   return processed