SQL server 2005-- using Fulltext index to replace 'Like' to improve the Performance

Like is famous by its slowness, however it can be supported by normal DB index you have been created.

select id
from my_table
where site_id = 121 and my_name like '%admin%'

we create and it is index site_id and my_name columns as its non_clustered index. the execution plan shows index seek followed.It takes more than 6 seconds to execute and its reads as high as 20,000

It takes 90% time on 'like', so Want to find a replacement for such a 'Like'. Anyway, i searched 'contains()' out, looks like cool!

Enable Fulltext index on your table:

EXEC [YOURDB].[dbo].[sp_fullext_database] @action = 'enable'

Right click that table -> Define a Fulltext index -> select column name to be indexed: here i am choosing 'my_name' column

update your SQL statement
select id
from my_table
where site_id = 121 and contains (my_name like '"*admin*"')

It is even 6 times faster for my test case.take less than 1 seconds. and its reads reduce to 150!! OMG, I do not know the principle behind it, but it rocks for this case anyway!

An example on Google Suggest case with Watir

One of my friend ask if Watir can give a demo on "Google suggest test" (selenium treat it well,I　guess). However, I was not finding an example on line for this case. Due to I am a fan of Watir, so I want to know this as well, here comes mine version, Enjoy!

require "watir"

# setup time out threshold
waiter = Watir::Waiter.new(8)

ie = Watir::IE.new

ie.goto("http://www.google.com/")

ie.text_field(:name, 'q').set('dev')


begin
#wait until find the google suggestion list, otherwise wait until timeout
waiter.wait_until {ie.row(:class, /gac_/).exists?}


rescue Exception => e
puts " Timeout, there is no suggestion list for this Keyword search"
puts " now end the test"

# sleep 2 seconds so that let human take a look :)
sleep 2

else
num_suggestions = ie.table(:class, 'gac_m').row_count_excluding_nested_tables()
rand_pick = rand(num_suggestions - 1)+1;
puts clickable_text = ie.table(:class, 'gac_m').row_values(rand_pick)
ie.cell(:text,"#{clickable_text}").click();

flag = ie.contains_text(/#{clickable_text}/);
if flag == nil
f = File.new("Error_page" + Time.now.to_i.to_s + ".html", "w+")
f << ie.html
raise "Error pages detected"
end

ensure
puts "pretty cool ending"
ie.close()

end

BTW, here is what I get from Selenium IDE（Xpath it powerful however it should be the last choice for me）, seems useless for me:

selenium.open("/");
selenium.type("q", "ab");
selenium.click("//tr[4]/td");
selenium.waitForPageToLoad("30000");

Sometimes, I am a little bit sad about less people talked about Watir, they are moving to Selenium more and more, i am not against Selenium/Webdriver, however i do not want to see Watir group growth slow down.Go Watir Go!

WebDriver in Selenium2.0 FAQ in short

Usually ,I always take look at FAQ to see if this tool did have what i need and its pros/cons:

Q: Why WebDriver:
A: WebDriver is becoming part of Selenium2.0.The main contribution that WebDriver will make is its API and the native drivers(which means WebDriver controls the browser itself).

Q: How do I handle pop up windows?

A: WebDriver offers the ability to cope with multiple windows. This is done by using the "WebDriver.switchTo().window()" method to switch to a window with a known name. If the name is not known, you can use "WebDriver.getWindowHandles()" to obtain a list of known windows. You may pass the handle to "switchTo().window()".

Q: WebDriver fails to find elements / Does not block on page loads
A: Solution: Use the Wait class to wait for a specific element to appear. This class simply calls findElement over and over, discarding the NoSuchElementException each time, until the element is found (or a timeout has expired). Since this is the behaviour desired by default for many users, we're working on implicitly-waiting classes

Q: Why is it not possible to interact with hidden elements?

Since a user cannot read text in a hidden element, WebDriver will not allow access to it as well.
However, it is possible to use Javascript execution abilities to call getText directly from the element: ((JavascriptExecutor) driver).executeScript("return arguments0.getText();", ...)

Run some smoke cases in Netbeans IDE, it is cool till now. But I am a Watir fan for quite a while:)

Endless wait by adding Synchronizing Timer

Here is one bug found in JMeter on Synchronizing Timer, they have adopted in their source code right now :) https://issues.apache.org/bugzilla/show_bug.cgi?id=48901

All the threads will perform endless wait by adding Synchronizing Timer.

This issue is much easier reproduced under a heavy load. I was using 100
threads to a simple HTTP request sampler with adding a Synchronizing Timer,
Groupsize set to 100 as well.

please see the attachment which is the stack trace of 100 threads doing a
endless wait.

Some initial invstigation for this issue:

Here is the current source code:

public long delay() {
synchronized (sync) {
timerCounter[0]++;
final int groupSz = getGroupSize();
final int count = timerCounter[0];
if (
(groupSz == 0 && count >=
JMeterContextService.getNumberOfThreads())
||
(groupSz > 0 && count >= groupSz)
) {
sync.notifyAll();
} else {
try {
sync.wait();
} catch (InterruptedException e) {
log.warn(e.getLocalizedMessage());
}
}
timerCounter[0]=0; // Reset for next time
}
return 0;
}

The main problem from my perspective is after one thread call sync.notifyAll();
,which means after the lock has been released,it will execute
timerCounter[0]=0;(correct me if i am wrong)

After chaning the code as following(move "timerCounter[0]=0;" in front of
"sync.notifyAll();", it does not impact any business impact, but make more
sense to me), and the issue disappear:
public long delay() {
synchronized (sync) {
timerCounter[0]++;
final int groupSz = getGroupSize();
final int count = timerCounter[0];
if (
(groupSz == 0 && count >=
JMeterContextService.getNumberOfThreads())
||
(groupSz > 0 && count >= groupSz)
) {
timerCounter[0]=0; // Reset for next time
sync.notifyAll();
} else {
try {
sync.wait();
} catch (InterruptedException e) {
log.warn(e.getLocalizedMessage());
}
}
}
return 0;
}

How to make your own JMeter build

Sometimes you may need to change the source code of JMeter to do some validation or even make a add-ons, how to build your own Jmeter:
Pre-condition: install Ant in advance
1. Download src.zip and binary.zip from http://jakarta.apache.org/site/downloads/downloads_jmeter.cgi
2. Unpack both zips into same directory
3. Copy bin and lib directory from binary to src
4. Cd to src directory from "start/run/cmd"
5. Type "ant download_jars" to get a few additional jars
6.Type "ant" command
7. It will generate a build folder automatically
8. You can directly use bin/Jmeter.bat to run your own JMeter version

Beatiful picture made in Twitter

I love this picture not due to its poor performance(Twitter's performance is great!), but it shows us the real status, most important point: it is really beautiful designed picture :)



And most important, they take some actions called: "What Causes Whales"
http://engineering.twitter.com/2010/02/anatomy-of-whale.html

Access SQL server 2005 by ruby + ADO

I followed the version of the code created by David Mullet to help me to access a SQL server to do some DB testing and further data processing based on data output.

This is pretty straight forward solution, i like it! :)

require 'win32ole'

class SqlServer
# This class manages database connection and queries
attr_accessor :connection, :data, :fields
attr_writer :username, :password

def initialize(host, username = 'sa', password='')
@connection = nil
@data = nil
@host = host
@username = username
@password = password
end

def open(database)
# Open ADO connection to the SQL Server database
connection_string = "Provider=SQLOLEDB.1;"
connection_string << "Persist Security Info=False;"
connection_string << "User ID=#{@username};"
connection_string << "password=#{@password};"
connection_string << "Initial Catalog=#{database};"
connection_string << "Data Source=#{@host};"
connection_string << "Network Library=dbmssocn"
@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)
end

def query(sql)
# Create an instance of an ADO Recordset
recordset = WIN32OLE.new('ADODB.Recordset')
# Open the recordset, using an SQL statement and the
# existing ADO connection
recordset.Open(sql, @connection)
# Create and populate an array of field names
@fields = []
recordset.Fields.each do |field|
@fields << field.Name
end
begin
# Move to the first record/row, if any exist
recordset.MoveFirst
# Grab all records
@data = recordset.GetRows
rescue
@data = []
end
recordset.Close
# An ADO Recordset's GetRows method returns an array
# of columns, so we'll use the transpose method to
# convert it to an array of rows
@data = @data.transpose
end

def close
@connection.Close
end
end

How to use this:

#Setup the Connection to DB
db = SqlServer.new('IP(without Port if 1433)', 'sa', 'pwd')
sp = db.open('Your_DB_name')
#execute SQL statement
db.query("Your_SQL_Statement;")
# and Stored procedure as well
db.query("exec your_stored_procedure para1, para2, etc");
#such as @starttime='2009-11-15 11:16:00',@endtime='2009-11-16 23:25:00'"
# output column name
puts field_names = db.fields
cust = db.data
# out put rowcounts
puts cust.size
# output value of certain rows by array
puts cust[0].inspect
#close DB connection
db.close

Dummy version of Http Client in Ruby

RubyCook book's Real-World HTTP Client looks relative complex for me, so i would like to re-organize some code to make it work to send simple requests.

require 'net/http'
require 'uri'

def Http_fetch(uri_str,action='get',data ='',limit = 10)

url = URI.parse("#{uri_str}")

#Get method
if limit ==10 && action == 'get'
res = Net::HTTP.start(url.host, url.port) {|http2|
http2.get(url.path)
}
puts "Get : " + res.code
end

#Post method
if limit ==10 && action == 'post'

http = Net::HTTP.new(url.host, url.port)
res = http.post(url.path, data)
raise "POST FAILED:"+res.inspect unless res.is_a? Net::HTTPOK
puts "Post : " + res.code
return res

end

#Redirects
if limit <10 && limit >0
res = Net::HTTP.get_response(URI.parse(uri_str))
puts "Redirect : " + res.code
end

raise ArgumentError, 'HTTP redirect too deep' if limit == 0

case res

when Net::HTTPSuccess
return res

when Net::HTTPRedirection
puts res['location']
fetch(res['location'], '', '',limit - 1)

else
res.error!
end

end

data_post = "username=***@gmail.com&password=***&language=en"
# Testing Get method
res1 = Http_fetch('http://www.infoq.com/')

# Testing Post method--login to infoq.com
res2 = Http_fetch('http://www.infoq.com/login.action','post',data_post)

# Testing Redirects--this leads me redirect to http://www.ruby-lang.org/en/
res3 = Http_fetch('http://www.ruby-lang.org/')

If you are using Mac OS or Linux, this post can be more helpful: http://fiatdev.com/2009/07/22/patron-0-4

SQL load testing with JMeter

Jmeter can easily trigger SQL statement or Stored Procedure.
by configuring following items, you can send a request to DB directly:

 DB URL:jdbc:sqlserver://yourIP:port(1433 as default value);DatabaseName=Dbname  
 JDBC Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver  
 username/password:**/**  
 note: please put "sqljdbc4.jar" to your Jmeter/lib directly  

Before running your testing against limited prepared parameters, then you may need to clean up the DB cache between 2 rounds of tests. So that the performance data can be reliable.

here is one way taken from : http://www.sql-server-performance.com/tips/dbcc_commands_p1.aspx

DBCC DROPCLEANBUFFERS: Use this command to remove all the data from SQL Server's data cache (buffer) between performance tests to ensure fair testing. Keep in mind that this command only removes clean buffers, not dirty buffers. Because of this, before running the DBCC DROPCLEANBUFFERS command, you may first want to run the CHECKPOINT command first. Running CHECKPOINT will write all dirty buffers to disk. And then when you run DBCC DROPCLEANBUFFERS, you can be assured that all data buffers are cleaned out, not just the clean ones.

Example:

DBCC DROPCLEANBUFFERS

And some of posts recommend to run following 2 commands in order to get testing readings from scratch:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

So I adopted before running next round of testing:

CHECKPOINT 30
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE