Number of rows inserted by hour

Almost all the time, when your database is large enough, you will be curious to know how busy the database is, and one of the ways is to find it is to look at the number of inserted row per hour.
The idea is pretty simple: Select records by date, group by the hour and count. It does get interesting when we start thinking about var types we are using and io statistics.
Here's the first query I wrote just to get the job done:

select datepart(hh,createDate) [hour],
count(datepart(hh,createdate)) RecordCount 
from myBusyTable
where CONVERT(VARCHAR(10),createDate, 101) = '01/01/2011'
group by datepart(hh,createDate))

where createDate is a datetime when the record was first inserted in the database. Note that I am using varchar to compare my date.

It took me 2 secs to run it. Set the IO STATISTICS ON and the logical reads was 9520. Changed the query to get data for seven days, and it took 40 secs.

Of course,  better way to do it would be to use datetime variables:

declare @startDate datetime
declare @endDate datetime
set @startDate = '01/01/2011'
set @endDate = @startDate + 1 -- goes until midnight next day
select datepart(hh,createDate) [hour],
count(datepart(hh,createdate)) RecordCount 
from myBusyTable
where createDate between @startDate and @endDate
group by datepart(hh,createDate)

This one completed is ms. And the logical reads reduced to 67 (from 9520).

And, if I need to run it for multiple days, I simply group it by date, and hour like below:

declare @startDate datetime
declare @endDate datetime

set @startDate = '01/01/2011'
set @endDate = @startDate + 7 -- goes until midnight next day
select convert(varchar(10),createDate, 101) date,
datepart(hh,createDate) [hour],
count(datepart(hh,createdate)) RecordCount 
from myBusyTable
where createDate between @startDate and @endDate
group by convert(varchar(10),createDate, 101), datepart(hh,createDate)
order by convert(varchar(10),createDate, 101), datepart(hh,createDate)


Here's the output:

Some useful batch scripts

Working with scripts is always fun. You can do so much stuffs with so less commands, and if you are dealing with doing some kind of operation on files / directories, sending remote commands, and even running sql scripts, scripts are the way to go.

In this post, I’ve tried to compile some scripts that I’ve used time and time again. Hopefully, this will help others.

• Calculating current datetime.
  The output of this script would be in format MMDDYYYY_HHSS
  
  set hhmm=%Time:~0,2%%Time:~3,2%
  if "%hhmm:~0,1%"==" " set hhmm=0%hhmm:~1,3%
  set DATETIME=%Date:~4,2%%Date:~7,2%%Date:~10,4%_%hhmm%
  echo %DATETIME%
  
  
• Creating a txt file with current datetime suffix.
  
  set FILENAME=Test_%DATETIME%.txt
  echo %FILENAME%>%FILENAME%
  
  
• Creating a ftp script on the fly, executing it and deleting it.
  
  echo open FTP_IP>FtpScript.txt
  echo FTP_USER>>FtpScript.txt
  echo FTP_PASS>>FtpScript.txt
  echo pwd>>FtpScript.txt
  echo put FILENAME>>FtpScript.txt
  echo bye>>FtpScript.txt
  
  REM Execute the ftp script
  ftp -s:FtpScript.txt
  
  REM Delete the ftp script
  del FtpScript.txt
  
  
• Splitting file contents with a “space” delimiter.
  
  for /f "tokens=* delims= " %%1 in (myFile.txt) do (
  echo contents: %%1
  echo.
  )
  
  
• Splitting file contents by a delimiter, and finding word at a exact location.
  
  Assuming the contents of file is: My name is Sudhir Chhetri, to find the "Sudhir" (location 4), the script would look like:
  
  for /f "tokens=1-4* delims= " %%1 in (output.txt) do (
  echo firstname=%%4
  )
  
  
• Check if a variable is null or empty string.
  
  Assuming my variable name is AAA, the script would look like:
  if "%AAA%null"=="null" (
  REM do something
  )
  
  
• Check to see if file exists
  
  if exist "C:\temp.txt" echo temp.txt exists
  
  
• Checking number of arguments.
  
  if "%4"=="" echo fourth param missing
  
  
• Finding last modified file in a directory
  
  set Location=C:\myLocation
  for /f "delims=" %%x in ('dir %Location% /od /b') do set recent=%%x
  
  where /od tells to order by date and /b tells to give just filename
  
  
• Running a sql file
  
  osql -S DBSERVER -d DBNAME -n -i SQLFILE –E
  
  
• Executing commands in remote machine
  
  psexec SERVERNAME –u USERNAME –p PASSWORD cmd /c “COMMANDS”
  
  Psexec.exe is a part of Sysinternals Process Utilities provided by Microsoft. More info about psexec.exe can be found at http://technet.microsoft.com/en-us/sysinternals/bb897553