seajey: Electronic Knight (Default)
Как-то столкнулся с необходимостью, в процедуре, задать порядок сортировки результирующего запроса через входящую переменную. Динамический SQL - не выход, а другого способа, кроме как лесенки IF'ов и множественного повторения SELECT'а не придумалось.

Задача возникла снова, и в этот раз получилось найти более лаконичный, в тоже время вполне понятный вариант. И так:

DECLARE @SortDirection dShortString SET @SortDirection = 'DESC' 

SELECT * 
FROM wlmUsers
ORDER BY 
CASE 
    WHEN @SortDirection = 'ASC' 
        THEN wlmUserID 
END ASC,
CASE    
    WHEN @SortDirection = 'DESC' 
        THEN wlmUserID 
END DESC
seajey: Electronic Knight (Default)
Недавно выяснил интересный момент: если табличная функция возвращает большое количество записей и нам в последствии нужно делать JOIN к другим таблицам, то предпочтительней использовать следующий метод.

Создаём временную таблицу:

CREATE #WorkingTable TABLE
(
ID int,
Value float
)

Создаём кластерный индекс по таблице:

CREATE CLUSTERED INDEX #table_index1 ON #WorkingTable(ID)

Забираем данные из табличной функции:

INSERT INTO #WorkingTable
(
ID,
Value
)
SELECT
f.ID,
f.Value
FROM dbo.fSelectData(@inID) AS f

Делаем итоговую выборку с джойном на нужный справочник:

SELECT
st.FullDescription,
wt.Value
FROM #WorkingTable AS wt
INNER JOIN SomeTable AS st ON st.ID = wt.ID

Правда, на мой наивный взгляд, заморачиваться с отдельной временной таблицей стоит, только если табличная функция выдаёт большой объём данных (десятки тысяч записей и больше).
seajey: Electronic Knight (Default)
Милую штучку тут на днях вычитал:

Вызов процедуры EXEC sp_depends [Object_Name] в MSSQL Studio даст нам список всех зависимых объектов для любого существующего в базе [Object_Name], будь то процедура, функция, таблица или представление. По полям, правда, уже не работает.

Правда, на все 100% sp_depends доверять нельзя - View Dependencies из контекстного меню Object Explorer в некоторых случаях почему-то более информативный.
seajey: Electronic Knight (Default)
Есть у нас хороший клиент, активных пользователь БизнесМенеджерАгро - основного продукта enterprice-части Wilmark'а. Сейчас у них, в связи с объединением с другой компанией, тоже нашего клиента, кстати, большие пертурбации, в том числе и со стандартом отчётности. В процессе перехода к новому формату они заказали нам серию отчётов на начисление различных расходов. Среди них есть отчёт, выдающий расчётные тарифы на перевозку ж/д транспортом реализованного товара.

Тарифные ставки для него берутся из паспорта сделки. И вот, внезапно, ставки глобально поменялись. И сей неприятный факт нужно учесть. Старый механизм уже не работает, замену мы ещё не успели перенести - и дело это не быстрое, а отчётность требуется прямо сейчас. Конечно, всегда можно поправить данные в паспорте сделки, но по разным соображениям, это неудобный и непродуктивный вариант.

Среди артефактов в системе был найден доселе неиспользуемый справочник с характерным названием "Ж/д тарифы" - по сути это табличка в базе со станцией отправки, станцией назначения и тарифом на перевозку между ними. На первое время сойдёт, а там уже нормальный блок маршрутов на подходе.
В-общем, я думал, что придётся поломать голову и повыдирать волосьев, но реальность оказалась не такой безрадостной.
Read more... )
seajey: Electronic Knight (Default)
Еще парочка простых, но полезных финтов. 1. Есть запрос к реальной клиентской базе: SELECT ArticleName FROM Articles WHERE isTotal <> 1 В результате артикулов возвращается 28 штук, хотя всего в базе 4605 различных артикулов. Проблема в том, что в большинстве записей в поле isTotal забито значение NULL, что суть есть отсутствие значения. Поэтому в условие нужно доработать: WHERE isTotal <> 1 OR isTotal IS NULL Либо применить такую конструкцию: ISNULL(isTotal, 0) <> 1 В итоге меньше символов и без заморочек со скобочками, если понадобится добавить ещё одно условие. 2. Допустим есть некий сложный запрос: SELECT Foo FROM Bar WHERE isFoo = 1 Мы его прооптимизировали и теперь он работает существенно быстрей. Но чтобы убедиться, что результат выдачи не изменился можно сделать так: SELECT Foo FROM Bar WHERE isFoo = 1 INTERSECT SELECT Foo FROM Bar WHERE isFoo = 1
seajey: Electronic Knight (Default)
Так НЕ работает, хотя казалось бы всё логично: DECLARE @RowsNumber int = 10; SELECT TOP @RowsNumber * FROM ShopSales Но стоит только добавить волшебных скобочков: DECLARE @RowsNumber int = 10; SELECT TOP (@RowsNumber) * FROM ShopSales SQL Server вдруг сразу же начинает понимать, что от него требуется.
seajey: Electronic Knight (Default)
Широко известный в узких кругах проект Архивы Кубикуса на этой неделе изволил изрядно тормозить. Не сказать, чтобы такой поведение ранее за ним не замечалось, но в этот раз случай был просто таки вопиющий. Недолгое расследование указало на одного из виновников тормозов: процесс sql-сервера кушал максимум доступных вычислительных ресурсов. В качестве СУБД используется MSSQL Server 2005, в нём из коробки нет Activity Monitor’а - он появился только в 2008-версии. Но можно смотреть статистику по используемым объектам через стандартный сервис Reports.

Самой ресурсоёмкой оказалась процедура пересчёта Читательского профиля. Несколько лет назад мы её уже оптимизировали, добавив кэширование результатов. Это достаточно радикально улучшило производительность и на тот момент этих изменений хватило. Но очевидно, здесь есть простор для оптимизаций, но не в этот раз. Больше всего ресурсов подсистемы IO занимала процедура, выводящая десятку форумных тем, отсортированную по времени последнего сообщения. Процедура не слишком большая, но исполнена в виде одного SELECT’a, включающего в себя несколько подзапросов, один из которых двойной вложенности. Не все СУБД любят такие вещи и нормально из переваривают, так что был смысл попробовать развернуть подзапросы в курсор.

На нашем внутреннем тестовом сервере (база 2008 года) эта процедура отрабатывала за 5 секунд, а в развёрнутом виде - меньше секунды. И каково же было моё удивление, когда на боевом сервере старая хранимка (она же хранимая процедура) выиграла у оптимизированной две секунды: 2-3 с против 4-5.

В процессе тестирования нового варианта процедуры, запускал её в SQL-студии с подключенным с Executional Plan’ом, и при анализе плана вычитал рекомендацию, что неплохо бы построить несколько индексов по двум таблицам на разные поля. Студия обещала, что производительность поднимется на ~50% и 20% соответственно. Попробовали создать индексы на боевом сервере - старая хранимка ушла в ноль, а новая ускорилась на две секунды.

И чего я раньше на индексы не обращал внимание? Хотя, они, конечно, не панацея от всех бед - помогают в случае сортировок и при использовании условий WHERE, но могут тормозить массовую вставку, например.

В общем, создал ещё несколько индексов для засветившихся в общей статистике процедур, а Алексей - наш веб-программист, ограничил вызов процедуры “горячей десятки тем” на главной странице сайта пятиминутным интервалом. За прошедшие три дня жалоб на тормоза пока не поступало. А я уже прикинул в каких проектах было бы полезно создать пяток-другой индексов.
seajey: Electronic Knight (Default)
Уважаю людей, знающих, чего им для счастья не хватает.
Тут проснулся один клиент - оказывается, они нам не просто так деньги за поддержку платят, а вполне могут заказать доработки существующего или же совершенно новый отчёт.

Большим плюсом для меня оказалось, что что-то очень похожее я уже делал - есть рабочее и отлаженное решение, осталось только перенести на сервер шаблон и необходимые процедуры/функции, ну и поработать напильником, молотком да стамеской: адаптировать код под MSSQL Server 2000 (кстати, давно пора на свалку, на редкость не удобно после 2005/2008 с ним работать), выкинуть лишнее и добавить недостающее.

На всё про всё ушло два дня. Заказчик доволен, доволен и я (что так легко отделался).

Вот бы со всеми так :(

Коллега уходила в отпуск на три недели, и меня перекинули на её фронт. Так, я только со стороны наблюдал за её мучениями, а теперь и сам вляпался.

Например, неделю уже бьюсь над одним отчётом, сделанным из кусочков других. Не без помощи старших коллег, мой отчёт почти по всем пунктам совпал с проверочным. Считаем затраты на финансирование - не сходится, хоть ты тресни. Разобрали оба отчёта до винтика, обнаружили, что проверочный здесь неправильно считает. Придумали способ как решить проблему, заодно и производительность немного поднять, разработали план, приступили к выполнению - звонок по скайпу: давайте повернём на 90 градусов и всё переиграем :(
seajey: Electronic Knight (Default)
Есть таблица со статьями учёта.
Нужно написать функцию, которая бы для входящей статьи выдавала бы полный список подстатей.
У статьи может быть несколько уровней вложенности, и нужно средствами T-SQL сделать рекурсивный обход.
В итоге всё получилось достаточно просто. Хотя лично я и помучился пару часов.

Читать дальше... )
seajey: Electronic Knight (Default)
Посылаю луч ненависти в Редмонд. Это ж надо было так исхитриться и испохабить SQL Studio:
В MSSQL Studio 2005 скорость выдачи результата исполнения запроса в GRID-форму была, конечно, не мгновенной, но и никакого неудовольствия не вызывала. Перешёл на Studio 2008, заодно сменив рабочую машину на куда более мощную - и началось.
Выдача на пять строчек - секунды через 3-5 появляются строки, потом ещё секунда три он думает и только потом его отпускает. Выдача на сотню строк, да ещё и в несколько Result Pane'ов - тоска... Иногда на несколько минут задумывается, а в последнее время студия стала просто сжирать максимум памяти (доступные для 32битных приложений 2 гига из 4) и молча падать - с OutOfMemoryException судя по всему.
Читать дальше... )
seajey: Electronic Knight (Default)
Один из коллег на работе недавно вспомнил, что как-то делал в MSSQL Studio такую штуку:
пишешь имя процедуры, возможно ещё определенную команду перед ней, жмёшь Enter или другой шорткат и, бац, студия студия вытаскивает текст процедуры в текущем Query. Очень удобно - не нужно лезть в Object Explorer, спускаться по дереву вниз и потом ещё искать нужную процедуру. Иной раз на весь процесс минута уходит, что раздражает. Собственно, меня такая возможность тоже заинтересовала, хотя и непонятно, сработает ли в Express-версии.

Начал гуглить и пока нашёл следующие хинты:

1. В Query Editor'е напечатать имя user-created процедуры или функции, выделить её и нажать ALT+F1

В итоге получится два Result Pane'а: в первом имя процедуры, владелец, тип и дата создания; во втором будут перечислены параметры и их свойства

2. Напечатать
sp_helptext @objname = 'ИмяПроцедурыИлиФункции'
выделить и нажать F5
В Result Pane будет выведен текст искомой процедуры или функции. Обе фишки по своему полезны, но это не то, что хотелось бы получить :(
Продолжаю поиск.
seajey: Electronic Knight (Default)
В своё время для моего босса (а потом и для меня тоже), который отнюдь не новичок в программировании и работе с базами данных, оказалась, по его словам, приятным открытием следующая штука:

SELECT *
INTO NewTable
FROM OldTable
WHERE <Условие>

Читать дальше... )
seajey: Electronic Knight (Default)
Как-то было мне задание в один наш отчёт добавить два дополнительных столбца.
На первый взгляд ничего сложного, но может так получиться, что все потроха перетрясти придётся - были уже случаи.

Открываю отчёт в редакторе, смотрю откуда он данные получает. Ага, процедура с параметрами. Открываю студию, смотрю процедуру - приткнуться там некуда, нужно лезть на уровень ниже, в функцию.

В функции кода раз в 10 больше - какие-то хитрые вычисления с группировками через временные таблицы.
Но интересно другое: для того, чтобы получить исходные данные, запускается другая процедура, пишущая результаты в отдельную таблицу в БД, уже к которой обращается функция.

В итоге добавил два новых поля в ту таблицу, поправил процедуру, и последовательно протащил все изменения наверх, в отчёт.

Как-то так.

Хе-хе

Apr. 28th, 2010 11:16 am
seajey: Electronic Knight (Default)
Комментарий (мой) в коде процедуры:

-- Для нужд тестирования делаем столицей Волгоград
seajey: Electronic Knight (Default)
Сравнение MSSQL Server с MySQL.

Много пунктов, один из них:

* Автоматические обновления. Для выполнения обновлений безопасности SQL Server интегрирован со службой Microsoft Update. У MySQL нет средств автоматического обновления.

Ну, не знаю как в Windows, но обычно это забота пакетного менеджера.
seajey: Electronic Knight (Default)
У нас в компании два основных подразделения: по разработке веб-сайтов, и по созданию корпоративных информационных систем.
И тот и другой департамент работает с базами данных, но немножко по-разному.

Веб-программисты в качестве ключа таблицы любят использовать простой интежеровский счётчик, а мы, соответственно, наоборот  - GUID'ы. И друг на друга смотрим примерно так:
"Нафига заморачиваться с гуидами, когда есть счётчик"
vs
"Зачем с этим счётчиком самому себе проблем подкидывать?"

Вот лично я не очень люблю каунтеры:
1. при неправильных/неверных/ошибочных join'ах таблиц могут вылезти на свет божий трудновыявляемые ошибки из-за неуникальности ключа, по которому соединялись таблички.
Помню на кубикусе был прикол с поменявшимися местами пользователем, создавшим в библиотеке писательскую страничку,  и автором, кому эта страница была посвящена.
Всего-то один неаккуратный UPDATE ...

2. в таблицу нельзя вставить запись с предопределённым ключём - чтобы этот ключ узнать, нужно сначала запись создать. Да и то это не наверняка.

GUID'ы в этом куда отношении проще: если при вставке не указать ключ - он сгенеририруется сам, через функцию newid(), в то же время ключик можно создать предварительно и запомнить в переменной для дальнейшего использования.

Единственно неудобство GUID'а - он длинный как длиннокот, 128 бит как-никак.
seajey: Electronic Knight (Default)
На работе я в основном имею дело с MSSQL Server 2005 Express, отличающейся от старшей версии отсутствием утилит импорта, SQL Agent'а, отвечающего за выполнение заданий по расписанию, ограничением по процессору и по максимальному количеству памяти, доступного  SQL-серверу. Ещё чутка обрезанная студия - например, на предмет поддержки сессии и автокомплита.

На серверах у клиентов стоят более функциональные версии, код, в основном, пишется локально, с последующим переносом на "боевой" сервер.
Перед тем как накатить изменения, очень желательно сделать бекап базы. Чем проще оный сделать - тем выше вероятность того, что бэкап будет действительно произведён, а из интерфейса студии (как в Express-варианте, так и в полной версии) это делать тоскливо (особенно, если резервную копию делаешь не первый десяток раз).
По этому поводу я сделал для себя скрипт и пару табличек - хранить статистику.
Собственно, скрипты.

Read more... )
Спасибо за внимание.
seajey: Electronic Knight (Default)
Иногда от него больше проблем, чем пользы:

DECLARE @inYear int

DECLARE @inMonth int

DECLARE @decWorkingDate datetime

-- простенькую, понятную конструкцию

SET @decWorkingDate = cast(@inYear + '-' + @inMonth + '-' + '01' AS datetime)

-- пришлось заменить на более громоздкую и мозголомную:

SET @decWorkingDate = convert(datetime, str(@inYear) + '-' + str(@inMonth) + '-' + '01', 102)


Чуть поразмыслив можно понять, почему первый SET неверный, но глядя на содержимое @decWorkingDate,  этот вывод сделать труднее. На статике компилятор/парсер просто выдал бы ошибку.

А в данном конкретном случае даже юнит-тесты не помогли бы. Да и сложно их для SQL писать.


ASP -> PHP

Jun. 2nd, 2009 11:12 pm
seajey: Electronic Knight (Default)
Народ, в той части нашей компании, что занимается разработкой сайтов, начал мигрировать с древнего ASP на PHP.
Впрочем остальной стэк остался прежним: Windows,  MSSQL Server, IIS.

Впрочем, LAMP маленькими шажками, но проникает.

Так вот, портирование начали с админки. В процессе были слышны сетования, что некоторые вещи в ASP'е проще делались, много матов на ; в конце строк и прочее.

Самое сложное AFAIR, с чем пришлось столкнуться, это аналог в MSSQL мускульного limit (x,y). Раньше то он вообще не применялся, но в книжке по PHP идёт через раз.

Помнится, на Кубикусе пришлось повозиться с оптимизацией выдачи результата  подбора по профилю. В итоге сделали кэширование в БД предыдущей выдачи, но вот появился ещё один способ поднять производительность.
 
Погуглив, нашли немало воркэраундов разной степени громоздкости. Встречались и откровенно черезжопные варианты, пока остановились на следующем:

SELECT
rank,
ArticleCode,
ArticleName
FROM  (SELECT
           row_number() OVER (ORDER BY ArticleCode) AS  rank,
           ArticleCode,
           ArticleName
           FROM Articles) AS f
WHERE rank BETWEEN x AND y
ORDER BY
rank

Работает только в MSSQL Server 2005 и выше, впрочем MSSQL 2000 как БД для сайтов у нас больше не встречается.

seajey: Electronic Knight (Default)
seajey: Electronic Knight (Default)
В данный момент пытаюсь подружить между собой MS SQL Studio и subversion.
Пока дома в виртуалке, но потом сделаю и на работе, а то надоел  бардак с кучей файлов раскиданных по папкам по постоянно менящейся системе. Да и перееду скоро на новую машинку - весь софт всё равно с нуля придётся ставить.

Есть уверенность, что в конце концов это сделать получится, но вот насколько удобно будет в этой связке работать?

Profile

seajey: Electronic Knight (Default)
SeaJey

September 2013

S M T W T F S
123456 7
8 910 11121314
15161718192021
22232425262728
2930     

Syndicate

RSS Atom

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Sep. 25th, 2017 06:10 am
Powered by Dreamwidth Studios