SHARE
TWEET

Untitled

a guest Aug 12th, 2016 110 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/bin/bash
  2.  
  3. SQL[0]="
  4. SELECT SQL_NO_CACHE zbozi_id, zbozi_cena, mnozstvi, rez_mnozstvi, COALESCE(mnozstvi - rez_mnozstvi, mnozstvi) as volne
  5. FROM zbozi
  6. JOIN (SELECT sum(sklad_mnozstvi) as mnozstvi, zbozi_id FROM sklad WHERE sklad_vyskladneno is null and umisteni_id = 1 GROUP BY zbozi_id) as skl USING (zbozi_id)
  7. LEFT JOIN (SELECT SUM(polozka_mnozstvi) as rez_mnozstvi, zbozi_id
  8.                    FROM polozka p
  9.                    join objednavka using (objednavka_id)
  10.                    join objednavka_stav on objednavka_stav_id = obj_stav_id
  11.                    JOIN umisteni USING (umisteni_id)
  12.                    WHERE umisteni_vyrizeno = 0
  13.                    AND p.aktivni = '1'
  14.                    and objednavka_stav_vyrizeno = 0 GROUP BY zbozi_id) as rez USING (zbozi_id)
  15. WHERE COALESCE(mnozstvi - rez_mnozstvi, mnozstvi) > 0
  16. "
  17. SQL[1]="
  18. SELECT SQL_NO_CACHE SUM(CASE WHEN (skladem.mnozstvi - (CASE WHEN rezervovane.mnozstvi IS NULL THEN 0 ELSE rezervovane.mnozstvi END)) > 0 THEN skladem.mnozstvi - (CASE WHEN rezervovane.mnozstvi IS NULL THEN 0 ELSE rezervovane.mnozstvi END) ELSE 0 END) as volne
  19. FROM (SELECT SUM(sklad_mnozstvi) as mnozstvi, zbozi_id
  20.      FROM sklad
  21.      WHERE sklad_vyskladneno IS NULL AND umisteni_id = 1
  22.      GROUP BY zbozi_id) as skladem
  23. LEFT JOIN (SELECT SUM(p.polozka_mnozstvi) AS mnozstvi, zbozi_id
  24.      FROM polozka p
  25.      INNER JOIN objednavka o ON  o.objednavka_id = p.objednavka_id
  26.      INNER JOIN objednavka_stav os ON  os.objednavka_stav_id = o.obj_stav_id
  27.      INNER JOIN umisteni u on u.umisteni_id = p.umisteni_id
  28.      WHERE umisteni_vyrizeno = '0'
  29.      AND p.aktivni = '1' AND objednavka_stav_vyrizeno = '0'
  30.      GROUP BY zbozi_id) as rezervovane ON (skladem.zbozi_id = rezervovane.zbozi_id)
  31. "
  32. SQL[2]="
  33. SELECT SQL_NO_CACHE polozka.polozka_id, polozka.zbozi_id, polozka.polozka_mnozstvi, polozka.umisteni_id, polozka.objednavka_id, obj_stav_id, zbozi_dostupnost, polozka_nazev, postovne_id
  34. FROM polozka
  35. LEFT JOIN objednavka USING (objednavka_id)
  36. LEFT JOIN objednavka_stav ON (objednavka.obj_stav_id = objednavka_stav.objednavka_stav_id)
  37. LEFT JOIN umisteni ON (polozka.umisteni_id = umisteni.umisteni_id)
  38. LEFT JOIN zbozi USING (zbozi_id)
  39. WHERE objednavka_stav_vyrizeno !=1 and umisteni_vyrizeno!=1 and polozka_mnozstvi>0
  40. and polozka.aktivni=1 and objednavka.aktivni=1 and polozka.zbozi_id!=13658
  41. and objednavka_stav.objednavka_stav_id!=17 ORDER BY polozka.polozka_id ASC
  42. "
  43. SQL[3]="
  44. SELECT SQL_NO_CACHE dodavatel_id, DATEDIFF(CURRENT_DATE, sklad_objednani_objednano) as pocet_dni_na_ceste, sum(sklad_objednani_mnozstvi) as kusu
  45. FROM sklad_objednani_
  46. WHERE sklad_objednani_temp = 0 AND sklad_objednani_objednano IS NOT NULL AND sklad_objednani_doruceno IS NULL AND sklad_objednani_aktivni = 1 AND dodavatel_id > 0
  47. GROUP BY dodavatel_id, pocet_dni_na_ceste
  48. "
  49. SQL[4]="
  50. SELECT SQL_NO_CACHE max(cv.cv_kod)
  51.                FROM cennik_velkoobchodu cv
  52.                JOIN cv_knv_detaily cr ON (cv.cv_kod = cr.cv_kod AND cv.dodavatel_id = 46)
  53.                LEFT JOIN carovykod ON (carovykod_ean = cv_ean)
  54.                WHERE cv_sparovano = 0 AND cv_zpracovano = 1 AND cv_novinka = 1 AND cv_cena_bez_dph > 0 AND cv_ean IS NOT NULL
  55.                AND cv_ean != '' AND LENGTH(cv_ean) = 13 AND cv_ean LIKE '978%' AND LENGTH(cv_nazev) >= 3 AND carovykod_ean IS NULL
  56.                AND skladovost > 0 AND dostupnost = 1 AND dostupnost_typ = 1 AND pocet_dni_k_doruceni = 0 AND obrazek != '' AND obrazek IS NOT NULL AND obrazek_update IS NOT NULL AND nakupka_czk > 0 AND dph IN (7,19)
  57.                AND LENGTH(cr.vydavatel) < 64 AND LENGTH(isbn) <= 20
  58.                GROUP BY ean
  59.                HAVING count(ean) = 1
  60.                LIMIT 100
  61. "
  62. SQL[5]="
  63. SELECT SQL_NO_CACHE jazyk, count(*) as pocet, unifikace_jazyku_zkratka
  64. FROM cv_knv_detaily
  65. LEFT JOIN unifikace_jazyku ON (jazyk = unifikace_jazyku_vyraz)
  66. WHERE jazyk != '' AND jazyk IS NOT NULL
  67. GROUP BY jazyk, unifikace_jazyku_zkratka
  68. HAVING count(*) > 9
  69. ORDER BY unifikace_jazyku_zkratka IS NOT NULL,pocet DESC
  70. "
  71.  
  72. DB_NAME="knihy_cme"
  73.  
  74. MARIA_HOST="cme-maria.cjpu7au3nbbl.eu-central-1.rds.amazonaws.com"
  75. MARIA_PORT="3306"
  76. MARIA_USER="rds_root"
  77. MARIA_PASS="eeC0hei8tul"
  78.  
  79. MYSQL_HOST="cme-mysql.cjpu7au3nbbl.eu-central-1.rds.amazonaws.com"
  80. MYSQL_PORT="3306"
  81. MYSQL_USER="rds_root"
  82. MYSQL_PASS="ies7uWooGhah"
  83.  
  84. POSTG_HOST="cme-postgre.cjpu7au3nbbl.eu-central-1.rds.amazonaws.com"
  85. POSTG_PORT="5432"
  86. POSTG_USER="rds_root"
  87. POSTG_PASS="phur0coo4Oen"
  88.  
  89.  
  90. COUNT=$1
  91.  
  92. echo "Testuji MariaDB:"
  93. TOTAL_TIME=0
  94. for ((i=0; i<${#SQL[@]}; i++))
  95. do
  96.     START=$(date +%s.%N)
  97.     for j in $(seq 1 $COUNT)
  98.     do
  99.         mysql -h$MARIA_HOST -P$MARIA_PORT -u$MARIA_USER -p$MARIA_PASS $DB_NAME -e"${SQL[$i]}" > /dev/null
  100.     done
  101.     END=$(date +%s.%N)
  102.     TIME=$(bc -l <<< "scale=5; ($END-$START)/$COUNT")
  103.     TOTAL_TIME=$(bc -l <<< "$TOTAL_TIME+$TIME")
  104.     echo "Prumerny beh prikazu $((i + 1)) po $COUNT spusteni: $TIME"
  105. done
  106. TOTAL_AVERAGE=$(bc -l <<< "scale=5; $TOTAL_TIME/(${#SQL[@]})")
  107. echo "Celkovy prumerny beh prikazu: $TOTAL_AVERAGE"
  108.  
  109. echo $'\nTestuji Postgres:'
  110. TOTAL_TIME=0
  111. for ((i=0; i<${#SQL[@]}; i++))
  112. do
  113.     START=$(date +%s.%N)
  114.     for j in $(seq 1 $COUNT)
  115.     do
  116.         psql=$(echo "${SQL[$i]}" | sed 's/SQL_NO_CACHE//')
  117.         if [ $i -eq 3 ]
  118.         then
  119.             psql=$(sed "s/DATEDIFF(CURRENT_DATE, sklad_objednani_objednano)/DATE_PART('day', CURRENT_DATE::timestamp - sklad_objednani_objednano::timestamp)/" <<< $psql)
  120.         fi
  121.         PGPASSWORD=$POSTG_PASS psql -h$POSTG_HOST -p$POSTG_PORT $DB_NAME $POSTG_USER -c"$psql" > /dev/null
  122.     done
  123.     END=$(date +%s.%N)
  124.     TIME=$(bc -l <<< "scale=5; ($END-$START)/$COUNT")
  125.     TOTAL_TIME=$(bc -l <<< "$TOTAL_TIME+$TIME")
  126.     echo "Prumerny beh prikazu $((i + 1)) po $COUNT spusteni: $TIME"
  127. done
  128. TOTAL_AVERAGE=$(bc -l <<< "scale=5; $TOTAL_TIME/(${#SQL[@]})")
  129. echo "Celkovy prumerny beh prikazu: $TOTAL_AVERAGE"
  130.  
  131. echo $'\nTestuji MySQL:'
  132. TOTAL_TIME=0
  133. for ((i=0; i<${#SQL[@]}; i++))
  134. do
  135.     START=$(date +%s.%N)
  136.     for j in $(seq 1 $COUNT)
  137.     do
  138.         mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS $DB_NAME -e"${SQL[$i]}" > /dev/null
  139.     done
  140.     END=$(date +%s.%N)
  141.     TIME=$(bc -l <<< "scale=5; ($END-$START)/$COUNT")
  142.     TOTAL_TIME=$(bc -l <<< "$TOTAL_TIME+$TIME")
  143.     echo "Prumerny beh prikazu $((i + 1)) po $COUNT spusteni: $TIME"
  144. done
  145. TOTAL_AVERAGE=$(bc -l <<< "scale=5; $TOTAL_TIME/(${#SQL[@]})")
  146. echo "Celkovy prumerny beh prikazu: $TOTAL_AVERAGE"
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top