Переменная в sql запросе


Табличная переменная в sql запросе

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 using MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Diagnostics; using System.IO; using System.Net; using System.Windows.Forms;   namespace WindowsFormsApplication1 {     public partial class Form1 : Form     {         // Класс         Link Peremen = new Link();           public Form1()         {             InitializeComponent();         }           // 1. Запрос к таблице spisok_catalog и ответ в comboBox1.Items.Add         private void Form1_Load(object sender, EventArgs e)         {             try             {                 string constring = "datasource=host;port=3306;username=USER;password=PASS";                 string Query = "select * from h92817_base.spisok_catalog";                 MySqlConnection conDataBase = new MySqlConnection(constring);                 MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);                 MySqlDataReader myReader;                   conDataBase.Open();                 myReader = cmdDataBase.ExecuteReader();                 while (myReader.Read())                 {                     // Переменная запроса поля Name в comboBox1.Items.Add                     string Name = myReader.GetString("Name");                     comboBox1.Items.Add(Name);                 }                 conDataBase.Close();             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }           }           private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)         {             // 2. Запрос к таблице spisok_catalog по имени из comboBox1.Text и ответ в Peremen.PeremenCatalogName             try             {                 string constring = "datasource=host;port=3306;username=USER;password=PASS";                 string Query = "select * from h92817_base.spisok_catalog where GameName='" + comboBox1.Text + "'";                 MySqlConnection conDataBase = new MySqlConnection(constring);                 MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);                 MySqlDataReader myReader;                   conDataBase.Open();                 myReader = cmdDataBase.ExecuteReader();                 while (myReader.Read())                 {                     // Переменная запроса поля CatalogName в свойство Peremen.PeremenCatalogName                     string CatalogName = myReader.GetString("CatalogName");                     Peremen.PeremenCatalogName = CatalogName;                 }                 conDataBase.Close();             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }               // запрос к выбранному каталогу из свойства Peremen.PeremenCatalogName и ответ в checkedListBox1.Items.AddRange             try             {                             string constring = "datasource=host;port=3306;username=USER;password=PASS";                 string Query = "select * from h92817_base." + Peremen.PeremenCatalogName + "";                 MySqlConnection conDataBase = new MySqlConnection(constring);                 MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);                 MySqlDataReader myReader;                   conDataBase.Open();                 myReader = cmdDataBase.ExecuteReader();                 while (myReader.Read())                 {                     // Переменная с свойством Peremen.PeremenCatalogName в checkedListBox1.Items.AddRange                     checkedListBox1.Items.Clear();                     checkedListBox1.Items.AddRange(Links.Load("").ToArray());                 }                 conDataBase.Close();             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }         }           // Выбор папки         private void btnObzor_Click(object sender, EventArgs e)         {             DialogResult result = folderBrowserDialog1.ShowDialog();             textBox1.Text = folderBrowserDialog1.SelectedPath;         }           // Загрузка файлов         private void btnLoad_Click(object sender, EventArgs e)         {             foreach (var item in checkedListBox1.CheckedItems)             {                 var webClient = new WebClient();                   // ссылка                 string link = (item as Link).Path;                   // имя файла                 string downloadFileName = Path.GetFileName(link);                   //загрузка                 webClient.DownloadFileAsync(new Uri(link), folderBrowserDialog1.SelectedPath + "" + downloadFileName);             }         }     }       class Link     {         public string PeremenCatalogName { get; set; }         public string Path { get; set; }         public string Name { get; set; }           public override string ToString()         {             return Name;         }       }       class Links : List<Link>     {         public static Links Load(string url)         {             //создаем Links             var res = new Links();             //подключаеся к БД             using (var connection = new MySqlConnection("datasource=host;port=3306;username=USER;password=PASS"))             {                 //создаем запрос к БД                 // Как передать переменную в класс, таким образом "select * from h92817_base." + Peremen.PeremenCatalogName + "" ?                 // Или + comboBox1.Text +                 var command = new MySqlCommand("select name, link from h92817_base.catalog_1;", connection);                 //открываем коннект к БД                 connection.Open();                   //Заполняем коллекцию res данные из базы данных                 using (var reader = command.ExecuteReader())                     while (reader.Read())                         res.Add(new Link { Name = reader.GetString(0), Path = reader.GetString(1) });                 connection.Close();             }             return res;         }     } }

forundex.ru

Sql переменная в запросе

Вопрос: Табличная переменная VS вложенный запрос

Добрый день!

Отлаживая запрос, наткнулся на неожиданную ситуацию.Работа с табличной переменной в mssql 2016 происходит на порядки дольше.Был подготовлен тестовый скрипт:

DECLARE @CurrentDate DATE = '2017-02-15' -- Определение границ прошлого месяца DECLARE @LeftDateTimeMargin DATETIME = DATEADD(MONTH, DATEDIFF( MONTH, 0, @CurrentDate )-1, 0) DECLARE @RightDateTimeMargin DATETIME = DATEADD(MONTH, DATEDIFF( MONTH, 0, @CurrentDate ), 0) -- debug -- SELECT @CurrentDate as CurrentDate, @LeftDateTimeMargin as LeftDateTimeMargin, @RightDateTimeMargin as RightDateTimeMargin -- Создание табличной переменной для сохранения выборки данных за целевой период DECLARE @SourceData TABLE ( [id] [uniqueidentifier] NOT NULL PRIMARY KEY, [event_datetime] [datetime] NOT NULL, [base_id] [uniqueidentifier] NOT NULL, [event_id] [uniqueidentifier] NOT NULL, [user_id] [uniqueidentifier] NULL, [metaname] [nvarchar](300) NOT NULL, [duration] [int] NULL, [product_id] [uniqueidentifier] NOT NULL, [server_event_datetime] [datetime] NOT NULL, [aeh_id] [uniqueidentifier] NULL ) -- Заполнение табличной перемнной данными с отбором (число строк = 6 599 436) INSERT INTO @SourceData ( [id], [event_datetime], [base_id], [event_id], [user_id], [metaname], [duration], [product_id], [server_event_datetime], [aeh_id] ) SELECT [id], [event_datetime], [base_id], [event_id], [user_id], [metaname], [duration], [product_id], [server_event_datetime], [aeh_id] FROM [StatSoftwareUsageData] WHERE server_event_datetime >= @LeftDateTimeMargin AND server_event_datetime < @RightDateTimeMargin -- ОЧЕНЬ ДОЛГО РАБОТАЕТ, БОЛЕЕ 20+ МИНУТ - в качестве источника, заранее подготовленная выборка -- При исполнении запроса, sqlservr грузит 1 ядро на 100% SELECT DISTINCT [@SourceData].[metaname] FROM @SourceData LEFT JOIN [StatMetanames] ON [StatMetanames].[metaname] = [@SourceData].[metaname] WHERE [StatMetanames].[id] IS NULL -- РАБОТАЕТ НОРМАЛЬНО (20 сек) - в качестве источника, обычная таблица -- При исполнении запроса, sqlservr грузит все ядра --SELECT -- DISTINCT [StatSoftwareUsageData].[metaname] --FROM [StatSoftwareUsageData] --LEFT JOIN [StatMetanames] ON [StatMetanames].[metaname] = [StatSoftwareUsageData].[metaname] --WHERE -- [StatMetanames].[id] IS NULL AND -- server_event_datetime >= @LeftDateTimeMargin AND -- server_event_datetime < @RightDateTimeMargin Понятно, что по строке соединяться - не самая лучшая затея. Но вопрос не в этом.Вопрос - почему такая существенная разница, при работе с одинаковым числом записей?Табличная переменная на порядки медленнее чем запрос к исходной таблице.Есть ли возможность как-то использовать табличные переменные, не теряя производительность? Почему грузится 1 ядро при работе с табличной переменной?

Казалось бы, табличная переменная целиком в памяти, вместо того, чтобы 10 раз прицеплять вложенный запрос к исходной таблице,должно быть лучше создать 1 раз нужную выборку и везде её использовать...

Ответ: Нектотам,Нектотам
2. Какая структура у StatSoftwareUsageData и какие индексы?
Поля 1 в 1 как у @SourceData, индекс кластеризованный на [id] и не кластеризованный на [server_event_datetime].Нектотам
3. В случае с табличной переменной вы читаете из StatSoftwareUsageData и пишите в @SourceData 10 колонок, а в простом запросе - 2.
Выполняется 20 минут инструкция после комментария (@SourceData заполняется достаточно быстро).При этом, если в быстром запросе добавить все колонки из медленного, скорость исполнения не меняется.Нектотам
4. Табличная переменная живет в tempdb. Это вполне может привести к большим IO.
Разве не временные таблицы живут в TempDB? Тогда как табличные переменные в памяти?В IO сервер не упирается при исполнении медленного запроса.автор
5. В случае с табличной переменной сначала надо выгрести все записи, а потом уже делать join.
Причина, почему в табличной переменной много колонок, в то время, как тестовый запрос использует только одну -это тестовый запрос, тут минимум кода, но результрующая выборка со всеми колонками может использоваться.Т.е. идея заключалась в том, чтобы 1 раз сформировать выборку (а ведь она может быть сложной и долгой), положить результат куда-то и при необходимости использовать, вместо того, чтобы заново формировать выборку.автор
6. В случае с табличной переменной оптимизатор часто промахивается, особенно без option(recompile), т.к. статистики по табличной переменной нет.
Спасибо!Добавление "OPTION (RECOMPILE)" к медленному запросу полностью устраняет разницу, одновременно с этим исполнение параллелится.Это правильный ответ на вопрос.Теперь хоть понятно откуда и почему возникает разница в тестовом запросе.

TaPaKСпасибо.

aleks2Никогда не понимал таких людей. Ты поди эксперт во всём, раз допускаешь такие комментарии.Опимизация тестового примера не дает ответ на вопрос.

forundex.ru

Передать значение переменной VBA в конструкцию SQL запроса

Вопрос: Получение значения переменной из блока

Добрый день. Подскажите пожалуйста, есть ли какой-то способ передать значение переменной из блока в, например, дальнейший запрос? Например есть некоторый простой блок:
Oracle 11 SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 DECLARE   p_x NUMBER; BEGIN   SELECT ROUND(w.price_r, 0)     INTO p_x     FROM invoices w    WHERE w.invc_id IN (SELECT q.invc_invc_id                          FROM invoice_histories q                         WHERE q.ivst_ivst_id = 1                           AND q.end_date > SYSDATE)      AND w.inv_number LIKE ('%СПС_____34%');   dbms_output.put_line(p_x); END;
Результатом работы блока будет число - значение переменной p_x. Есть ли способ использовать ее в дальнейшем? З.Ы. Я знаю, что блок тут не нужен совсем, можно обойтись просто запросом, важно понимание есть ли способ или нет, т.к. в реально задаче в блоке обрабатываются несколько результатов запросов в виде мат. формулы и теперь требуется сравнивать промежуточные значения (входные параметры для формулы) с результатами отдельных выборок вне блока. Ответ: Нет. Имеется в виду другое. Сперва создается пакет
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 CREATE OR REPLACE PACKAGE          param_test IS     param1  varchar2(100);     PROCEDURE set_param1(p_param IN varchar2);     FUNCTION get_param1 RETURN varchar2; END; / CREATE OR REPLACE PACKAGE BODY          param_test IS     PROCEDURE set_param1(p_param IN varchar2)    IS     BEGIN         param1 := p_param;     END;       FUNCTION get_param1 RETURN VARCHAR2    IS     BEGIN         RETURN param1;     END; END; /
а потом делается что-то типа таких проверок его работы
SQL
1 2 3 4 EXEC param_test.set_param1('abcde'); SELECT param_test.get_param1 FROM dual; EXEC param_test.set_param1('fgphx'); SELECT param_test.get_param1 FROM dual;
Здесь я использую EXEC, чтобы не писать все это хозяйство в анонимных блоках. Если вводится несколько переменных, то либо на каждую пишется своя пара функций get-set, либо добавляется параметр p_name, а в коде делается переключение по этому параметру. Если требуется хранить переменные разных типов (date, varchar2, number), то на каждый тип делается своя пара get-set. Есть еще один способ передавать параметры в пределах сессии. Это записывать данные в локальный контекст сессии и вытаскивать их оттуда. Почитайте про пакет DBMS_SESSION ( в частности, DBMS_SESSION.SET_CONTEXT), функцию SYS_CONTEXT и предопределенный контекст 'CLIENTCONTEXT'

По поводу прав на таблицы. Возможна ситуация, когда таблица создается в одной схеме, а используют ее из другой схемы (один юзер создал, другой использует). В таких случаях надо дать юзеру, который использует, права на select, insert и, наверное delete для этой таблицы

forundex.ru