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. Есть ли способ использовать ее в дальнейшем? З.Ы. Я знаю, что блок тут не нужен совсем, можно обойтись просто запросом, важно понимание есть ли способ или нет, т.к. в реально задаче в блоке обрабатываются несколько результатов запросов в виде мат. формулы и теперь требуется сравнивать промежуточные значения (входные параметры для формулы) с результатами отдельных выборок вне блока. Ответ: Нет. Имеется в виду другое. Сперва создается пакет
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 для этой таблицы
